What does COUNT(DISTINCT status) return for four rows with statuses A, A, B and NULL?

## The rule `COUNT(DISTINCT <expression>)` performs two steps: throw away rows where the expression is NULL, then count the number of **different** remaining values. On the values `A, A, B, NULL` that leaves `{A, B}`, so the result is 2. Seen next to its siblings on the same four rows: ```sql SELECT COUNT(*) AS rows_total, -- 4 COUNT(status) AS with_a_value, -- 3 COUNT(DISTINCT status) AS distinct_values -- 2 FROM tickets; ``` Three numbers, three different questions: how many rows, how many rows carry a value, how many values exist. ## The asymmetry with row-level DISTINCT This is the part interviewers probe. `SELECT DISTINCT status FROM tickets` returns three rows: `A`, `B` and `NULL`. Row-level `DISTINCT` treats NULLs as duplicates of each other and collapses them into a single row — it keeps them. The aggregate does not: it removes NULL inputs before counting anything. So `COUNT(DISTINCT status)` is **not** the same as "the number of rows returned by `SELECT DISTINCT status`" whenever NULLs are present; it is one less. Anyone who has replaced a `SELECT DISTINCT x` list with a count in a dashboard has felt this discrepancy: two panes that should agree differ by exactly one. ## Empty and all-NULL groups If a group contains no rows, or every row's expression is NULL, `COUNT(DISTINCT ...)` returns **0**, not NULL. That makes it safe in comparisons and in denominators guarded by a zero check, unlike `SUM`, which is NULL over empty input. ## Where it is the right tool The classic uses are "how many different customers ordered this month" (`COUNT(DISTINCT customer_id)`) and "how many distinct products appear in this basket". The NULL-dropping is usually what you want there: a row whose `customer_id` is unknown should not be counted as a customer. The danger is the opposite case — a status, category or country column where NULL is a meaningful bucket ("not classified yet") that stakeholders expect to see in a count of categories. ## Making unknowns countable If NULL must count as its own category, replace it with a real value before the aggregate: ```sql SELECT COUNT(DISTINCT COALESCE(status, 'UNCLASSIFIED')) FROM tickets; ``` The caveat is collision: if `'UNCLASSIFIED'` also occurs as real data, the two merge and you undercount by one. Choose a sentinel that cannot appear, or report the unknowns separately as a second measure so nothing is folded together. ## A note on the interaction with DISTINCT elsewhere `COUNT(DISTINCT x)` de-duplicates within each group independently. In a grouped query, the same value appearing in two different groups is counted once per group — the distinctness is scoped to the group, not to the whole result. This trips people who expect the sum of per-group distinct counts to equal the overall distinct count; it does not, unless the value sets are disjoint. ## Answering it well Give the number, then the rule in one sentence ("different non-NULL values"), then volunteer the contrast with row-level `DISTINCT`, which shows you know that SQL's two de-duplication mechanisms disagree about NULL. Finish with the practical consequence: decide deliberately whether unknown means "no category" or "a category called unknown".