.-ALL------. >>-COUNT--(--+-+----------+--expression-+--)------------------->< | '-DISTINCT-' | '-*------------------------'
The COUNT function returns the number of rows or values in a set of rows or values.
The result of the function is a large integer and it must be within the range of large integers. The result cannot be null. If the table is a distributed table, then the result is DECIMAL(15,0). For more information about distributed tables, see the DB2(R) Multisystem book.
The argument of COUNT(*) 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(expression) or COUNT(ALL 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 non-null 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 duplicate values. The result is the number of values in the set.
If a sort sequence other than *HEX is in effect when the statement that contains the COUNT(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 sort sequence.
SELECT COUNT(*) INTO :FEMALE FROM EMPLOYEE WHERE SEX = 'F'Results in FEMALE being set to 19.
SELECT COUNT(DISTINCT WORKDEPT) INTO :FEMALE_IN_DEPT FROM EMPLOYEE WHERE SEX='F'Results in FEMALE_IN_DEPT being set to 6. (There is at least one female in departments A00, C01, D11, D21, E11, and E21.)
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.