Using the COUNT and COUNTDISTINCT functions

The COUNT function returns the number of records that have a value for an attribute. COUNTDISTINCT counts the number of distinct values for an attribute.

Using COUNT to count the number of records with values of attributes

The COUNT function counts the number of records that have values in a field for each GROUP BY result.

For example, the following records include Size and Color attributes:
Record 1: Size=small, Color=red, Color=white 
Record 2: Size=small, Color=blue, Color=green 
Record 3: Size=small, Color=black 
Record 4: Size=small 
The following statement returns the number of records for each size that have a value for the Color attribute:
RETURN result AS SELECT COUNT(Color) as Total 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 only three of the records have Color assignments.

Using COUNTDISTINCT to get 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 only be used for single-assign attributes, and not for multi-assigned attributes. Using a multi-assign attribute generates misleading results.

For example, for the following records:
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 
GROUP BY Size
The statement result is:
Record 1: Size=small, Total=2

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 2 because there are two unique values for the Color attribute: red and blue.