COUNT_BIG

The COUNT_BIG function returns the number of rows or values in a set of rows or values. It is similar to COUNT except that the result can be greater than the maximum value of integer.


                   .-ALL------.                    
>>-COUNT_BIG--(--+-+----------+--expression-+--)---------------><
                 | '-DISTINCT-'             |      
                 '-*------------------------'

expression: The argument values can be of any built-in data type other than a DataLink. XML is not allowed for COUNT_BIG(DISTINCT expression).

The result of the function is a decimal with precision 31 and scale 0. The result cannot be null.

The argument of COUNT_BIG(*) is a set of rows. The result is the number of rows in the set. A row that includes only null values is included in the count.

The argument of COUNT_BIG(expression) is a set of values. The function is applied to the set derived from the argument values by the elimination of null values. The result is the number of values in the set.

If a collating sequence other than *HEX is in effect when the statement that contains the COUNT_BIG(DISTINCT expression) is executed and the arguments are SBCS data, mixed data, or Unicode data, then the result is obtained by comparing weighted values for each value in the set. The weighted values are based on the collating sequence.

Examples

Refer to COUNT examples and substitute COUNT_BIG for occurrences of COUNT. The results are the same except for the data type of the result.
To count on a specific column, a sourced function must specify the type of the column. In this example, the CREATE FUNCTION statement creates a sourced function that takes any column defined as CHAR, uses COUNT_BIG to perform the counting, and returns the result as a double precision floating-point number. The query shown counts the number of unique departments in the sample employee table.
```
     CREATE FUNCTION RICK.COUNT(CHAR(19)) RETURNS DOUBLE
       SOURCE QSYS2.COUNT_BIG(CHAR());

     SET CURRENT PATH RICK, SYSTEM PATH 

     SELECT COUNT(DISTINCT WORKDEPT) FROM EMPLOYEE;
```