COUNT() GROUP BY ...

All functions > GROUP BY > COUNT() GROUP BY ...

Returns the number of values in the group.

Syntax

COUNT() | COUNT(DISTINCT expr)
 [ FILTER (WHERE condition) ]
  [ GROUP BY feature [, feature ...] ]

COUNT() | COUNT(DISTINCT expr)
 [ FILTER (WHERE condition) ]
  [ GROUP BY feature [, feature ...] ]

Notes

• COUNT() counts every row, including rows whose columns are NULL
• COUNT() takes no argument. Permissive mode rewrites COUNT(*) and COUNT(non-null constant) to COUNT(); COUNT(NULL) and COUNT(column) stay invalid
• To count non-NULL values, use COUNT_IF(expr IS NOT NULL) or COUNT() FILTER (WHERE expr IS NOT NULL)
• Use COUNT(DISTINCT expr) for distinct non-NULL identities (catalog size, unique keys)
• COUNT(PER key) is one contribution per key on a finer spine, not the same as COUNT(DISTINCT key)
• Returns 0 for empty groups
• Can be used with FILTER (WHERE ...) to filter before counting
• Can be used with GROUP BY for grouped counts

Examples

-- Count of rows
-- Count of non-NULL values
-- Distinct non-NULL values in one group
-- COUNT(DISTINCT) with TRANSFORM GROUP BY (one result row per group key)
RETURN
    f1 := ZIP(ARRAY(1, 2, 3, 4, 5) AS value).TRANSFORM(RETURN COUNT()).UNWRAP_ONE(),
    f2 := ZIP(ARRAY(1, NULL::BIGINT, 3, NULL::BIGINT, 5) AS value).TRANSFORM(
        RETURN COUNT() FILTER (WHERE value IS NOT NULL)
    ).UNWRAP_ONE(),
    f3 := ZIP(ARRAY(1, 2, 2, 3) AS value).TRANSFORM(
        RETURN COUNT(DISTINCT value)
    ).UNWRAP_ONE(),
    f4 := ZIP(ARRAY(1, 1, 2, 2) AS g, ARRAY(10, 20, 20, 30) AS value).TRANSFORM(
        RETURN
            g,
            cnt := COUNT(DISTINCT value) GROUP BY (g)
        ORDER BY g
    )
;