COUNT aggregate function

The COUNT function returns the number of rows or values in a set of rows or values.

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 is a large integer. The result cannot be null.

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

The argument of COUNT(expression) or COUNT(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(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 values 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 DSN8C10.EMP.

   EXEC SQL SELECT COUNT(*)
     INTO :FEMALE
     FROM DSN8C10.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(DISTINCT WORKDEPT)
     INTO :FEMALE_IN_DEPT
     FROM DSN8C10.EMP
     WHERE SEX = 'F';