SQL Reference

COUNT_BIG

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

The schema is SYSIBM.

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.

If DISTINCT is used, the resulting data type of expression must not have a length greater than 255 for a character column or 127 for a graphic column. The resulting data type of expression cannot be a LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK, distinct type on any of these types, or structured type (SQLSTATE 42907).

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(DISTINCT expression) is a set of values. The function is applied to the set of values derived from the argument values by the elimination of null and duplicate values. The result is the number of different non-null values in the set.

The argument of COUNT_BIG(expression) or COUNT_BIG(ALL expression) is a set of values. The function is applied to the set of values derived from the argument values by the elimination of null values. The result is the number of non-null values in the set, including duplicates.

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.
• Some applications may require the use of COUNT but need to support values larger than the largest integer. This can be achieved by use of sourced user-defined functions and setting the SQL path. The following series of statements shows how to create a sourced function to support COUNT(*) based on COUNT_BIG and returning a decimal value with a precision of 15. The SQL path is set such that the sourced function based on COUNT_BIG is used in subsequent statements such as the query shown.

   CREATE FUNCTION RICK.COUNT() RETURNS DECIMAL(15,0)
          SOURCE SYSIBM.COUNT_BIG();
   SET CURRENT FUNCTION PATH RICK, SYSTEM PATH;
   SELECT COUNT(*) FROM EMPLOYEE;
  

  Note how the sourced function is defined with no parameters to support COUNT(*). This only works if you name the function COUNT and do not qualify the function with the schema name when it is used. To get the same effect as COUNT(*) with a name other than COUNT, invoke the function with no parameters. Thus, if RICK.COUNT had been defined as RICK.MYCOUNT instead, the query would have to be written as follows:

    SELECT MYCOUNT() FROM EMPLOYEE; 
  

  If the count is taken on a specific column, the sourced function must specify the type of the column. The following statements created a sourced function that will take any CHAR column as a argument and use COUNT_BIG to perform the counting.

   CREATE FUNCTION RICK.COUNT(CHAR()) RETURNS DOUBLE
          SOURCE SYSIBM.COUNT_BIG(CHAR());
   SELECT COUNT(DISTINCT WORKDEPT) FROM EMPLOYEE;