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 non-NULL 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.

Note that the COUNT(1) syntax returns all records, including those with NULL values. For example, you can get the number of records in your data store as follows:

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.

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.

You should also take care when using COUNTDISTINCT on the corpus. For example, this statement:

SELECT COUNTDISTINCT(multiassign_attr) AS n FROM AllBaseRecords

will de-multiassign prior to aggregating, so it will not return the correct answer.

The correct way to do this is to group by the attribute, then count the results:

DEFINE a AS SELECT 1 AS n
FROM AllBaseRecords
GROUP BY multiassign_attr; 

RETURN b AS 
SELECT COUNT(n) AS n 
FROM a