COUNT() GROUP BY ...
All functions > GROUP BY > COUNT() GROUP BY ...
Returns the number of values in the group.
Syntax
COUNT([DISTINCT] expr | *) [ FILTER (WHERE condition) ] [ GROUP BY feature [, feature ...] ]
Notes
- Counts non-NULL values in the group
- NULL values are excluded from the count
- Returns 0 for empty groups
- Use COUNT(DISTINCT expr) for the number of distinct non-NULL values
- Can be used with WHERE clause to filter before counting
- Can be used with GROUP BY clause for grouped counts
- Use COUNT(*) to count all rows including NULLs
Examples
FeatureQL
SELECT
f1 := ZIP(ARRAY[1, 2, 3, 4, 5] AS value).TRANSFORM(SELECT COUNT(value)).UNWRAP_ONE(), -- Count of values
f2 := ZIP(ARRAY[1, NULL::BIGINT, 3, NULL::BIGINT, 5] AS value).TRANSFORM(SELECT COUNT(value)).UNWRAP_ONE(), -- Count of values excluding NULLs
f3 := ZIP(ARRAY[1, 2, 2, 3] AS value).TRANSFORM(SELECT COUNT(DISTINCT value)).UNWRAP_ONE(), -- Distinct non-NULL values in one group
f4 := ZIP(ARRAY[1,1,2,2] AS g, ARRAY[10,20,20,30] AS value).TRANSFORM(SELECT g, COUNT(DISTINCT value) GROUP BY g AS cnt ORDER BY g) -- COUNT(DISTINCT) with TRANSFORM GROUP BY (one result row per group key)
;Result
| f1 BIGINT | f2 BIGINT | f3 BIGINT | f4 VARCHAR |
|---|---|---|---|
| 5 | 3 | 3 | [{g: 1, cnt: 2}, {g: 2, cnt: 2}] |