COUNT function

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

The COUNT function counts the number of records that have non-NULL values in a field for each GROUP BY result. COUNT can be used with both multi-assign attributes (sets) and single-assign attributes.

For multi-assign attributes, the COUNT function counts all non-NULL sets in the group. Note that because sets are never NULL but can be empty, COUNT will also count 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.

The syntax of the COUNT function is:

COUNT(<attribute>)

where attribute is either a multi-assign or single-assign attribute.

COUNT examples

The following records include the single-assign Size attribute and the multi-assign Color attribute:

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
FROM ProductState
GROUP BY Size

The statement result is:

Record 1: Size=small, Total=4

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 4, because Records 1-3 have Color assignments (and thus return non-empty sets) and Record 4 does not have a Color assignment (and an empty set is returned).

If you are using COUNT with a multi-assign attribute and want to exclude empty sets, use a per-aggregate WHERE clause with the IS NOT EMPTY function, as in this example:

RETURN result AS
SELECT COUNT(Color) WHERE (Color IS NOT EMPTY) AS Total
FROM ProductState
GROUP BY Size

This statement result is:

Record 1: Size=small, Total=3

because the empty set for Record 4 is not counted.

COUNT(1) format

The COUNT(1) syntax returns a count of all records (including those with NULL values) in a specific collection. For example, you can get the number of data records in your Sales collection as follows:

RETURN Results AS
SELECT COUNT(1) AS recordCount
FROM SalesState
GROUP

The statement result should be an integer that represents the total number of data records.