COUNT_BIG aggregate function

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 an integer.

The schema is SYSIBM.

The argument values can be of any built-in data type other than a BLOB, CLOB, DBCLOB, or XML.

The result of the function is a decimal number 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) 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 nonnull values in the set, including duplicates.

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 redundant duplicate values. The result is the number of different nonnull values in the set.

Example 1: Set the integer host variable FEMALE to the number of females represented in the sample table DSN8D10.EMP.

   EXEC SQL SELECT COUNT_BIG(*)
     INTO :FEMALE
     FROM DSN8D10.EMP
     WHERE SEX = 'F';

Example 2: Set the integer host variable FEMALE_IN_DEPT to the number of departments that have at least one female as a member.

   EXEC SQL SELECT COUNT_BIG(DISTINCT WORKDEPT)
     INTO :FEMALE_IN_DEPT
     FROM DSN8D10.EMP
     WHERE SEX = 'F';

Example 3: To create a sourced function that is similar to the built-in COUNT_BIG function, the definition of the sourced function must include the type of the column that can be specified when the new function is invoked. In this example, the CREATE FUNCTION statement creates a sourced function that takes a CHAR column as input and uses COUNT_BIG to perform the counting. The result is returned 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()) RETURNS DOUBLE
          SOURCE SYSIBM.COUNT_BIG(CHAR());
   SET CURRENT PATH RICK, SYSTEM PATH;
   SELECT COUNT(DISTINCT WORKDEPT) FROM DSN8D10.EMP;

The empty parenthesis in the parameter list for the new function (RICK.COUNT) means that the input parameter for the new function is the same type as the input parameter for the function named in the SOURCE clause. The empty parenthesis in the parameter list in the SOURCE clause (SYSIBM.COUNT_BIG) means that the length attribute of the CHAR parameter of the COUNT_BIG function is ignored when Db2 locates the COUNT_BIG function.