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.
COUNTDISTINCT(<attribute>)where attribute is either a multi-assign or single-assign attribute.
Record 1: Size=small, Color=red Record 2: Size=small, Color=blue Record 3: Size=small, Color=red Record 4: Size=small
RETURN Result AS SELECT COUNTDISTINCT (Color) as Total FROM ProductState GROUP BY Size
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.
RETURN Result AS SELECT COUNTDISTINCT(Color) WHERE (Color IS NOT EMPTY) AS Total FROM ProductState GROUP BY Size
Record 1: Size=small, Total=2because the empty set for Record 4 is not counted.