count_distinct

On this page

Counts distinct non-NULL values exactly.

Syntax

count_distinct(<expr>)

Arguments

ArgumentTypeDescription
<expr>Any typeInput value.

Returns

Returns int. Ignores NULL values. Returns 0 for empty input or when every value is NULL.

Examples

Count checkout events and distinct buyers for each campaign:

VALUES
    ('spring', 'buyer-17'), ('spring', 'buyer-17'), ('spring', 'buyer-42'),
    ('loyalty', 'buyer-17'), ('loyalty', 'buyer-63'), ('loyalty', 'buyer-63'), ('loyalty', 'buyer-87')
SELECT $0 AS campaign, $1 AS buyer_id
GROUP BY campaign
AGGREGATE count() AS events, count_distinct(buyer_id) AS buyers
ORDER BY campaign
+----------+--------+--------+
| campaign | events | buyers |
+----------+--------+--------+
| loyalty  | 4      | 3      |
| spring   | 3      | 2      |
+----------+--------+--------+

A buyer can appear in more than one campaign. Adding campaign counts can therefore count the same buyer more than once.