What does SUM(o.amount) return when one order of 100 joins three order_items rows?
answer
- the join changes how many rows exist
- one parent row becomes several
- every copy carries the same amount
- the aggregate sees rows, not orders
- 100 gets added once per item
basics
~10 s300, not 100. The join repeats the order row once per matching item, so the same amount is added three times. That row multiplication is join fan-out, and it silently inflates SUM and COUNT.
solid answer
~50 sThe join produces three rows, one per matching `order_items` row, and each of them carries a full copy of the order's columns — including `amount = 100`. `SUM(o.amount)` does not know these are copies of one order; it adds every row it is given, so it returns **300**. This is join fan-out: joining a parent to a one-to-many child multiplies the parent's rows by the number of children, and any aggregate over parent columns is multiplied with them. `COUNT(*)` is inflated the same way — it counts joined rows, not orders. The fix is to stop the multiplication rather than to patch the number: aggregate `order_items` per `order_id` in a derived table or CTE first, then join that one-row-per-order summary to `orders`. `SELECT DISTINCT` does not help, because the item columns make each joined row genuinely different.
code
sql · 4 lines-- orders: (1, 100) order_items: (1, 'a'), (1, 'b'), (1, 'c')
SELECT SUM(o.amount) AS revenue -- returns 300, not 100
FROM orders o
JOIN order_items i ON i.order_id = o.order_id;go deeper
Be ready to trace a tiny join by hand: write out the rows the join produces, then apply the aggregate to those rows. Knowing that one order times three items equals three rows is the whole answer.
Explain the mechanics: the join emits pairs, the parent columns repeat, and additive aggregates scale with the repetition while MIN and MAX do not. Then show the pre-aggregate-then-join rewrite from memory.
Show how you catch this in review and in production: a control total, a COUNT(*) versus COUNT(DISTINCT pk) check, and suspicion of any report whose numbers moved when a table was added to the FROM clause.
Own the systemic angle: metrics assembled from wide multi-table joins drift as child tables grow, so the standard should be one aggregation layer per grain, with the grain of every derived table documented rather than inferred.
## The setup Assume two tables. `orders` has one row: `(order_id = 1, amount = 100)`. `order_items` has three rows, all with `order_id = 1`. The query is: ```sql SELECT SUM(o.amount) FROM orders o JOIN order_items i ON i.order_id = o.order_id; ``` The intuitive reading is "sum the order amounts", and the intuitive answer is 100. The actual answer is 300. ## Why the join multiplies rows A join does not "attach" the child rows to the parent row; it produces a new result whose rows are *pairs*. Conceptually the engine forms every combination of a left row and a right row and keeps the pairs where the `ON` predicate is true. Here one order pairs with three items, so three pairs survive. Each pair is a complete row containing every column of both sides, so `o.amount = 100` appears three times — not because the data is wrong, but because a one-to-many relationship, flattened into a rectangle, must repeat the "one" side. This is the general rule: joining a parent to a child on a key that is **not unique** in the child multiplies each parent row by its number of matching children. If some orders have 1 item and others have 20, the multiplication factor is different per row, which is exactly what makes the resulting error impossible to correct by dividing by a constant. ## What each aggregate does with the duplicates Aggregates are computed over the rows the earlier clauses hand them, and they have no notion of which rows came from the same original order: - `SUM(o.amount)` adds 100 three times → 300. - `COUNT(*)` counts joined rows → 3, though there is one order. - `COUNT(o.order_id)` counts non-NULL values in those rows → also 3. - `AVG(o.amount)` returns 100 here, but only by accident: with several orders it becomes an average weighted by each order's item count. - `MIN(o.amount)` and `MAX(o.amount)` are unaffected — repeating a value cannot change the smallest or largest one. That is the useful asymmetry: duplication breaks additive aggregates and leaves extremal ones alone. ## Seeing the multiplication Before trusting any aggregate over a join, look at the rows the aggregate sees. Strip the aggregate and select the raw joined rows, or compare counts: ```sql SELECT COUNT(*) AS joined_rows, COUNT(DISTINCT o.order_id) AS orders FROM orders o JOIN order_items i ON i.order_id = o.order_id; ``` If `joined_rows` exceeds `orders`, the order rows were multiplied, and every additive aggregate over `orders` columns in that query is wrong. ## Fixing it: aggregate before you join The reliable fix is to collapse the child side to one row per parent *before* joining, so the join becomes one-to-one and nothing multiplies: ```sql SELECT SUM(o.amount) AS revenue, SUM(i.item_count) AS items FROM orders o LEFT JOIN ( SELECT order_id, COUNT(*) AS item_count FROM order_items GROUP BY order_id ) i ON i.order_id = o.order_id; ``` Now `revenue` is 100 and `items` is 3. `LEFT JOIN` keeps orders that have no items at all; with an inner join those orders would vanish from the total. The same shape written as a `WITH` clause reads better when there are several children. A second, narrower fix applies when the only thing you need is a count of parents: `COUNT(DISTINCT o.order_id)` deduplicates by primary key and gives the right answer. It repairs counts of the parent and nothing else. ## What does not fix it - **`SELECT DISTINCT`** deduplicates whole result rows. The joined rows differ in their item columns, so nothing is removed; and even when it appears to work, it silently deletes legitimately identical parent rows. - **`SUM(DISTINCT o.amount)`** deduplicates *values*: two different orders that both cost 100 collapse into a single 100. - **Switching to `LEFT JOIN`** changes only which unmatched rows survive; a matched order still repeats once per item. - **`GROUP BY o.order_id`** gives a per-order total, but that per-order total is still 300. ## What to say in an interview Name the mechanism ("the one-to-many join multiplies the order row"), state the number, and go straight to the structural fix: pre-aggregate the child, then join the summary. Mentioning that `MIN`/`MAX` are immune while `SUM`/`COUNT`/`AVG` are not shows you understand *why*, not just that a rule exists.
- Does COUNT(*) in that same query suffer the same distortion?Yes. `COUNT(*)` counts joined rows, so it returns 3 — the item count, not the order count. If you want orders, `COUNT(DISTINCT o.order_id)` returns 1 because the primary key deduplicates the copies. If you want items, 3 is already correct, which is why mixed measures in one fanned-out query are so easy to misread.
- Does making it a LEFT JOIN avoid the inflation?No. `LEFT JOIN` only changes what happens to orders with *no* matching items: they survive with NULL-extended item columns instead of disappearing. An order with three items still produces three rows, so the amount is still added three times. Join type controls unmatched rows; it never controls multiplicity.
- Which aggregates are unaffected by fan-out?`MIN` and `MAX` over parent columns are safe, because repeating a value cannot change the smallest or largest one. `SUM` and `COUNT` are additive and scale with the duplication. `AVG` is the worst case: it survives a single-parent example unharmed but becomes a child-count-weighted average as soon as several parents fan out by different factors.
Joining is like stapling a photocopy of the order receipt to each item slip. Three slips means three receipts, and adding the receipts up counts the same money three times.
saying these in an interview costs you the question
- Says SELECT DISTINCT fixes an inflated SUM
- Thinks the join type, not the row count, causes it
- Claims the database deduplicates identical joined rows automatically
- Believes GROUP BY collapses the duplicates before summing
- Answers 100 because there is only one order