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
-- Count of values
f1 := ZIP(ARRAY(1, 2, 3, 4, 5) AS value).TRANSFORM(SELECT COUNT(value)).UNWRAP_ONE(),
-- Count of values excluding NULLs
f2 := ZIP(ARRAY(1, NULL::BIGINT, 3, NULL::BIGINT, 5) AS value).TRANSFORM(
SELECT COUNT(value)
).UNWRAP_ONE(),
-- Distinct non-NULL values in one group
f3 := ZIP(ARRAY(1, 2, 2, 3) AS value).TRANSFORM(
SELECT COUNT(DISTINCT value)
).UNWRAP_ONE(),
-- COUNT(DISTINCT) with TRANSFORM GROUP BY (one result row per group key)
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
)
;Result
| f1 BIGINT | f2 BIGINT | f3 BIGINT | f4 VARCHAR |
|---|---|---|---|
| 5 | 3 | 3 | [{g: 1, cnt: 2}, {g: 2, cnt: 2}] |