COUNTDISTINCT function

The COUNTDISTINCT function counts the number of distinct values for an attribute.

The COUNTDISTINCT function returns the number of unique values in a field for each GROUP BY result. COUNTDISTINCT can be used for both single-assign and multi-assigned attributes.

Note that because sets are never NULL but can be empty, COUNTDISTINCT will also evaluate a record with an empty set (that is, an empty set is returned for any record that does not have an assignment for the specified multi-assign attribute). See the second example below for how to ignore empty sets from the results.

The syntax of the COUNTDISTINCT function is:

COUNTDISTINCT(<attribute>)

where attribute is either a multi-assign or single-assign attribute.

COUNTDISTINCT example

The following records include the single-assign Size attribute and the multi-assign Color attribute:

Record 1: Size=small, Color=red
Record 2: Size=small, Color=blue
Record 3: Size=small, Color=red
Record 4: Size=small

The following statement returns for each size the number of different values for the Color attribute:

RETURN Result AS 
SELECT COUNTDISTINCT (Color) as Total
FROM ProductState 
GROUP BY Size

The statement result is:

Record 1: Size=small, Total=3

Because all of the records have the same value for Size, there is only one group, and thus only one record. For this group, the value of Total is 3 because there are two non-empty sets with unique values for the Color attribute (red and blue), and an empty set is returned for Record 4.

If you are using COUNTDISTINCT with a multi-assign attribute and want to exclude empty sets, use a WHERE clause with the IS NOT EMPTY function, as in this example:

RETURN Result AS
SELECT COUNTDISTINCT(Color) WHERE (Color IS NOT EMPTY) AS Total
FROM ProductState
GROUP BY Size

This statement result is:

Record 1: Size=small, Total=2

because the empty set for Record 4 is not counted.