COUNT DISTINCT and COUNT UNIQUE functions
The COUNT DISTINCT and COUNT UNIQUE functions return unique values.
The COUNT DISTINCT function returns the number
of unique values in the column or expression, as the following example
shows.
SELECT COUNT (DISTINCT item_num) FROM items;
If the COUNT DISTINCT function encounters NULL values, it ignores them unless every value in the specified column is NULL. If every column value is NULL, the COUNT DISTINCT function returns zero (0).
The UNIQUE keyword has the same meaning as the DISTINCT
keyword in COUNT functions. The UNIQUE keyword instructs the
database server to return the number of unique non-NULL values in
the column or expression. The following example calls the COUNT
UNIQUE function, but it is equivalent to the preceding example
that calls the COUNT DISTINCT function:
SELECT COUNT (UNIQUE item_num) FROM items;
If the Projection clause does not specify the DISTINCT or UNIQUE keyword of the SELECT statement, the query can include multiple COUNT functions that each includes the DISTINCT or UNIQUE keyword as the first specification in the argument list, as in the following example:
SELECT COUNT (UNIQUE item_num), COUNT (DISTINCT order_num) FROM items;