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.
The COUNT function counts the number of records that have non-NULL values in a field for each GROUP BY result.
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
RETURN result AS SELECT COUNT(Color) AS Total 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 only three of the records have Color assignments.
RETURN results AS SELECT COUNT(1) AS recordCount GROUP
The statement result should be an integer that represents the total number of records in your data set.
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.
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 GROUP BY Size
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.
SELECT COUNTDISTINCT(multiassign_attr) AS n FROM AllBaseRecordswill de-multiassign prior to aggregating, so it will not return the correct answer.
DEFINE a AS SELECT 1 AS n FROM AllBaseRecords GROUP BY multiassign_attr; RETURN b AS SELECT COUNT(n) AS n FROM a