Count distinct per group
Unique users per day, not just rows per day: COUNT(DISTINCT user_id) GROUP BY day, and how that differs from one distinct count overall.
Last updated
SELECT day, COUNT(*) AS events, COUNT(DISTINCT user_id) AS unique_users FROM events GROUP BY day ORDER BY day;
Why it’s written this way
COUNT(*) counts every row in the group, including a user who fired ten events that day ten separate times; COUNT(DISTINCT user_id) instead deduplicates user_id within each group before counting, so that same user contributes exactly once to unique_users. Both aggregates run over the same GROUP BY day, which is what makes them per-day figures rather than table-wide ones.
COUNT(DISTINCT user_id) GROUP BY day is not the same question as COUNT(DISTINCT user_id) with no GROUP BY: the ungrouped version counts how many distinct users appear anywhere in the whole table, while the grouped version restarts the distinct count inside each day, so a user active on three different days is counted three times, once per day, not once overall.
Edge cases to know
- →NULL user_id values are excluded from COUNT(DISTINCT ...) the same way NULLs are excluded from COUNT(column) — an anonymous-event row won't register as a 'unique user' at all, which may or may not match what the question intended.
- →Multiple COUNT(DISTINCT ...) expressions over different columns in one query (adding COUNT(DISTINCT session_id), say) run fine in DuckDB and Postgres, but some engines restrict or slow down noticeably with more than one per query.
- →COUNT(DISTINCT ...) needs an internal sort or hash-dedupe per group, so on a high-cardinality column over a large table it's measurably more expensive than a plain COUNT(*) — worth knowing before running it over an ungrouped, table-wide query.
Part by part
- COUNT(*)
- Every row in the group, regardless of NULLs. This is the raw event count and the denominator most engagement ratios need.
- COUNT(DISTINCT user_id)
- Distinct non-NULL values within the group. The two counts side by side are what turn a volume figure into a per-person one.
Common mistakes
COUNT(user_id)Counts non-NULL values without deduplicating, so a user with fifty events counts fifty times. It sits between the two useful numbers and answers neither question.
SUM(COUNT(DISTINCT user_id)) across days to get monthly uniquesDistinct counts do not add up — a user active on ten days is counted ten times. Monthly uniques must be computed from the raw rows over the whole month, not from the daily figures.
COUNT(DISTINCT user_id, session_id)Not valid in most databases, which accept only one expression. Concatenate the columns with a separator that cannot occur in either, or count distinct over a composite value.
Questions
Why can I not add daily unique counts together?
Because the same user appears on several days and would be counted once per day. Uniqueness is only meaningful over the exact window it was computed for, which is why weekly and monthly figures must be recomputed.
Does COUNT DISTINCT include NULL?
No, NULLs are excluded entirely. If a missing user_id is meaningful — anonymous events, say — count those separately rather than assuming the figure covers every row.
Why is it slow on large tables?
It has to track every value seen to deduplicate, which costs memory and often a sort. Approximate functions such as APPROX_COUNT_DISTINCT or HyperLogLog trade a small error for a large speed-up when exactness is not required.
How do I count distinct across several columns?
Concatenate them into one value with a separator that cannot appear inside either, then count distinct over that. Choosing the separator carelessly makes two different pairs collide into one.