What does SUM(DISTINCT amount) compute, and when would you actually want it?

## What the quantifier does Standard SQL lets an aggregate take a *set quantifier* before its argument: `ALL` (the default) or `DISTINCT`. With `DISTINCT`, the engine first reduces the multiset of argument values within the group to its distinct values, then applies the function to what is left. ```sql -- payments.amount holds 10, 10 and 25 SELECT SUM(amount) AS all_amounts, -- 45 SUM(DISTINCT amount) AS distinct_amounts; -- 35 ``` The crucial word is **values**. `DISTINCT` inside an aggregate never looks at whole rows and never considers a primary key; it only asks whether two evaluations of the argument expression produced the same value. Two unrelated customers who each paid exactly 50 produce one 50, because 50 equals 50. ## Where it is allowed The standard permits `DISTINCT` inside `COUNT`, `SUM` and `AVG`, which are the aggregates whose answers actually change when duplicates are removed. `MIN(DISTINCT x)` and `MAX(DISTINCT x)` parse but are no-ops: the smallest and largest members of a set do not depend on how many times each member appears. Writing them is harmless, but it signals confusion in a review. NULL handling does not change: the quantifier reduces duplicates among the non-null values, and the aggregate still ignores NULL as it always does. ## The legitimate use `SUM(DISTINCT …)` is right when the repeated value *is* one fact repeated, and the repetition carries no information. Typical shapes: - Each subscription tier has one price, and rows repeat the tier price per subscriber; summing the distinct prices gives the total catalogue price of the tiers offered. - A snapshot table repeats a per-account daily fee on every transaction row, and you want the sum of the fees, not the sum over the transactions. Notice what those have in common: the value functions as a *label* whose repetition is an artefact of the table's shape, and any two equal values really are the same thing. If it is possible for two distinct facts to carry the same number — which is exactly the situation with money — `DISTINCT` silently destroys one of them. ## The illegitimate use The most common reason people reach for `SUM(DISTINCT …)` is that a total came out too large and this made it smaller. That is not a fix; it is a second bug layered on the first. Consider three genuine payments of 50, 50 and 30: ```sql SELECT SUM(amount) FROM payments; -- 130, correct SELECT SUM(DISTINCT amount) FROM payments; -- 80, silently wrong ``` The total shrank, but not because duplicates were removed — because two customers happened to pay the same amount. `DISTINCT` inside an aggregate cannot distinguish "the same row counted twice" from "two different rows with equal values", because it never sees rows at all. When a total is inflated, the honest move is to establish *which rows* should contribute — deduplicate or pre-aggregate the row set first — and then sum every remaining row with a plain `SUM`. A useful diagnostic: if you cannot name the reason two equal values must be the same fact, `DISTINCT` does not belong inside the aggregate. ## AVG(DISTINCT …) is the same trap, sharper `AVG(DISTINCT rating)` averages the *distinct rating values*, not the ratings given. If 100 people rate something 5 and one person rates it 1, `AVG(rating)` is about 4.96 and `AVG(DISTINCT rating)` is 3 — the average of the two values 5 and 1. Both numbers are computable; only one answers "what is the average rating?". Interviewers like this example because the two results are far apart and the reason is purely semantic. ## Portability and cost notes The quantifier is standard and widely available for these aggregates, but do not assume every aggregate in a given engine accepts it — engines differ, particularly for the string- and array-building aggregates and for user-defined functions, so check yours. It is also worth knowing that `DISTINCT` forces the engine to materialise or sort the distinct values, which is real work rather than a free modifier — but *how* it does that is an execution concern, not part of the statement's meaning. ## Answering it well Define it precisely (values, not rows), show the 10/10/25 example, then volunteer the boundary: it is correct only when equal values are necessarily the same fact, and it is never the right response to a total that looks too big. That last sentence is what the interviewer is actually listening for.