How does the count distinct query work?

COUNT(*) counts rows; COUNT(DISTINCT user_id) counts each non-null user once per group. Dividing the two gives the average number of events per user. The distinct count is computed separately inside each month, so a user active in two months is counted in both.

What commonly goes wrong with count distinct in SQL?

Distinct counts do not add up across groups: unique users per month do not sum to unique users per year.

Count Distinct in SQL

Count distinct counts each different value once, which is how you get unique users, customers or products instead of rows.

Example query

This example assumes: events(id, user_id bigint, event_name text, occurred_at timestamptz). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT DATE_TRUNC('month', occurred_at)::date AS month,
       COUNT(*) AS events,
       COUNT(DISTINCT user_id) AS unique_users,
       COUNT(*)::numeric / NULLIF(COUNT(DISTINCT user_id), 0) AS events_per_user
FROM events
GROUP BY 1
ORDER BY 1;

How it works

COUNT(*) counts rows; COUNT(DISTINCT user_id) counts each non-null user once per group.
Dividing the two gives the average number of events per user.
The distinct count is computed separately inside each month, so a user active in two months is counted in both.

MySQL, BigQuery, SQL Server and SQLite differences

COUNT(DISTINCT) works in every major database. BigQuery and some warehouses also offer APPROX_COUNT_DISTINCT for very large tables, which is faster but approximate.

Common mistakes

Distinct counts do not add up across groups: unique users per month do not sum to unique users per year.
COUNT(DISTINCT x) ignores nulls, so rows without a user id disappear from the unique count.
Counting distinct after a join that duplicates rows hides the duplication in the distinct column but not in COUNT(*) or SUM.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

How many unique customers ordered each month?
How many different products were sold last week?