Why does COUNT(DISTINCT o.order_id) repair a fanned-out count but SUM(DISTINCT o.amount) not repair the total?

## What DISTINCT inside an aggregate actually does `COUNT(DISTINCT x)`, `SUM(DISTINCT x)` and `AVG(DISTINCT x)` are standard SQL: the aggregate first collapses the multiset of `x` values to its distinct values, then aggregates those. The crucial word is *values*. It is a deduplication of the column's contents, not of the rows those contents came from. Join fan-out duplicates **rows**. So `DISTINCT` inside an aggregate repairs fan-out exactly when "one distinct value" and "one original row" mean the same thing — that is, when the column is unique across the parent table. ## Why the primary key case works ```sql SELECT COUNT(DISTINCT o.order_id) AS orders FROM orders o JOIN order_items i ON i.order_id = o.order_id; ``` `order_id` is the primary key of `orders`: unique and not nullable. The join produced one row per item, so `order_id` appears once per item, but the set of distinct `order_id` values is exactly the set of orders that had at least one item. Deduplicating values and deduplicating rows coincide, so the count is right. Two caveats travel with this. `COUNT(DISTINCT col)` ignores NULLs, so counting a nullable column counts only the rows where it is present. And the guard is only as strong as the uniqueness: `COUNT(DISTINCT o.customer_name)` counts distinct *names*, merging two different customers who share one — a bug that has nothing to do with the join and everything to do with picking a non-key column. ## Why the sum case fails ```sql -- two orders, both amount = 100, each with 2 items SELECT SUM(o.amount) AS inflated, -- 400 SUM(DISTINCT o.amount) AS wrong -- 100 FROM orders o JOIN order_items i ON i.order_id = o.order_id; ``` The true total is 200. The plain `SUM` gives 400 because each order's 100 is added twice. `SUM(DISTINCT o.amount)` gives 100, because the distinct set of amounts is `{100}`. The repair overshoots: it removes the fan-out duplicates *and* the legitimate repetition of equal amounts across different orders. Worse, it is wrong on this data even with no join at all — `SELECT SUM(DISTINCT amount) FROM orders` also returns 100. A construct that gives the wrong answer on a single table is not a fan-out fix; it is a different bug that happens to move the number in the right direction sometimes. The same reasoning condemns `AVG(DISTINCT x)`: it averages the distinct values, which is a meaningful question only if you actually wanted "average of the distinct price points". ## What DISTINCT can and cannot be used for Use `COUNT(DISTINCT parent_pk)` when: - you need how many parent entities appear in a fanned-out result, and - the column is the parent's key, and - no additive measure over parent columns shares the same SELECT list. That last condition is the one people miss. In a query like `SELECT COUNT(DISTINCT o.order_id), SUM(o.amount) FROM orders o JOIN order_items i ...`, the count is right and the sum is wrong, and the correct count makes the wrong sum look trustworthy. Mixing a repaired measure with an unrepaired one in the same row is how fan-out reaches dashboards. ## The structural alternative When measures are in play, remove the multiplication rather than compensating for it: ```sql SELECT COUNT(*) AS orders, SUM(o.amount) AS revenue FROM orders o WHERE EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.order_id); ``` Here no join multiplies anything — the existence test filters without duplicating — so both aggregates are correct with no `DISTINCT` anywhere. If you also need a measure *from* the child, pre-aggregate it into a one-row-per-order derived table and join that. Both rewrites make `DISTINCT` unnecessary, which is the real goal: `DISTINCT` sprinkled through a SELECT list is a symptom that the query's grain is wrong. ## Interview framing State the distinction in one line — "DISTINCT deduplicates values; fan-out duplicates rows; they coincide only for a key column" — give the two-orders-of-100 counterexample, and finish by saying you would rather remove the fan-out than paper over it. Being explicit that `COUNT(DISTINCT pk)` is a *partial* guard, correct for counts and useless for sums, is the answer interviewers are listening for.