In SQL, how do you aggregate two one-to-many child tables in one query without cross-inflating totals?

## The bug: children multiply each other Consider `orders`, `payments` and `shipments`, each child holding several rows per order. The natural-looking query is: ```sql SELECT o.order_id, SUM(p.amount) AS paid, COUNT(s.shipment_id) AS shipments FROM orders o JOIN payments p ON p.order_id = o.order_id JOIN shipments s ON s.order_id = o.order_id GROUP BY o.order_id; ``` For an order with 2 payments and 3 shipments, the first join yields 2 rows, and the second pairs each of those with 3 shipments — 6 rows. Every payment now appears 3 times and every shipment 2 times, so `paid` is tripled and `shipments` doubled. Nothing errors, nothing warns; the report is simply wrong, and wrong by a *different* multiplier for every order, so no constant division repairs it. The general rule: joining `n` independent one-to-many children multiplies the parent row by the product of their match counts. Two children are enough to make the result unusable, and the distortion of each measure is driven by the *other* child's cardinality — the least intuitive part of the bug. ## The fix: one aggregation per grain A grain is the level a row describes: one row per order, one row per payment, one row per shipment. Mixing grains in one `FROM` clause is what breaks. So reduce each child to the parent's grain *first*, then join grain-to-grain: ```sql WITH pay AS ( SELECT order_id, SUM(amount) AS paid FROM payments GROUP BY order_id ), shp AS ( SELECT order_id, COUNT(*) AS shipments FROM shipments GROUP BY order_id ) SELECT o.order_id, COALESCE(pay.paid, 0) AS paid, COALESCE(shp.shipments, 0) AS shipments FROM orders o LEFT JOIN pay ON pay.order_id = o.order_id LEFT JOIN shp ON shp.order_id = o.order_id; ``` Each CTE is unique on `order_id` — `GROUP BY order_id` guarantees it — so both joins are one-to-one and no row is ever duplicated. The outer query needs no `GROUP BY` at all, which is itself a good signal that the grains now line up. Derived tables in the `FROM` clause work identically; the `WITH` form is just easier to read and to extend with a third child. ## Why LEFT JOIN and COALESCE An inner join to the summaries would silently drop orders that have no payments or no shipments, turning a correctness bug into a coverage bug. `LEFT JOIN` keeps every order and NULL-extends the missing summary columns. `COALESCE(pay.paid, 0)` then turns "no payments" into 0 rather than NULL, which matters because NULL propagates through arithmetic: `COALESCE(pay.paid, 0) - COALESCE(refund.amount, 0)` gives a number, while the un-coalesced version gives NULL for any order missing either side. ## Filters belong inside the summary If only settled payments count, put the predicate inside the CTE (`WHERE status = 'SETTLED'`), not in the outer `WHERE`. Filtering after a `LEFT JOIN` on a column from the right side turns the outer join back into an inner one and drops orders with no settled payment. Keeping every child's own predicates inside that child's summary is what makes this pattern compose: each CTE is independently readable and independently testable — you can run it alone and check that it returns one row per order. ## When you can skip the CTEs Two narrower situations do not need the full pattern. First, if only one child is joined and you only need a **count of parents**, `COUNT(DISTINCT o.order_id)` deduplicates by primary key. Second, if a child is genuinely one-to-at-most-one (enforced by a unique constraint on the join key), joining it directly is safe — there is no fan-out to prevent. The dangerous case is assuming uniqueness that the schema does not enforce; a key that happens to be unique today will not be after next month's data. ## What does not work `SELECT DISTINCT` over the six-row product does not help: the rows differ in payment and shipment columns, so none are removed, and even when it appears to work it destroys legitimately duplicate detail rows. `SUM(DISTINCT p.amount)` deduplicates values, so two genuine payments of the same size collapse into one. Dividing a measure by the other child's row count is arithmetic patching that breaks the moment a parent has zero children on one side. ## Interview framing Say the word *grain*, state the product rule (2 × 3 = 6 rows), and write the CTE-per-child rewrite. Add the `LEFT JOIN` plus `COALESCE` detail unprompted — that is the part that separates someone who has read about fan-out from someone who has shipped a report.