Review these examples before implementing the COUNT and COUNTDISTINCT functions.
The COUNT function counts the number of records with valid non-null values in a field for each GROUP BY result. For example, suppose there are four records with the value "small" in the Size field, and some of them have values in the Color field:
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 query "RETURN result AS SELECT COUNT(Color) as Total GROUP BY Size" will return the result:
Record 1: Size=small, Total=3
The query returns "3", because in the "small" result group, three records had valid Color assignments.
Note
The COUNT function is counting records with valid assignments (three in this example), not the number of different values that appear in the field (five in this example: red, white, blue, green and black).
For single-assigned properties, the COUNTDISTINCT function returns the number of unique, valid non-null values in a field for each GROUP BY result. For example, suppose there are four records with the value "small" in the Size field and different single values in the Color field:
Record 1: Size=small, Color=red Record 2: Size=small, Color=blue Record 3: Size=small, Color=red Record 4: Size=small
The query "RETURN result AS SELECT COUNTDISTINCT (Color) as Total GROUP BY Size" would return:
Record 1: Size=small, Total=2
The total is "2" because across all of the records in this group, there are two unique, valid, non-null values in the Color field: "red" and "blue."
Note
COUNTDISTINCT should never be used with multi-assigned properties. The results of the query may be misleading if records can have multiple values for one property (for example: Record 1: Size=small, Color=red, Color=white, Color=blue).
