Chapter 27 · advanced · 7 min
Joins that quietly inflate your numbers
This is the single most common bug a junior analyst ships, and it never errors. A JOIN that matches one row on one side to several rows on the other side — a fan-out — multiplies every downstream aggregate by however many matches came back. The query runs, the number looks plausible, and it's wrong.
We'll build the exact shape that causes it, watch a SUM double, and cover the two habits that catch it before it reaches anyone.
The dataset
An orders table (one row per order) joined against a order_items table (multiple rows per order) — the textbook one-to-many shape.
Schema
| order_id | INT |
| customer_name | TEXT |
| order_total | NUMERIC |
| item_id | INT |
| order_id | INT |
| item_name | TEXT |
Example data
One order, three line items
Aina's order has an order_total of 50.00 stored once, on the orders row. She bought three separate items, so order_items has three rows pointing at order_id = 1. Join the two tables and look at what happens to order_total.
orders
| order_id | customer_name | order_total |
|---|---|---|
| 1 | Aina | 50.00 |
| 2 | Bakri | 30.00 |
order_items
| order_id | item_name |
|---|---|
| 1 | Notebook |
| 1 | Pen |
| 1 | Stapler |
| 2 | Notebook |
result
| customer_name | order_total | item_name |
|---|---|---|
| Aina | 50.00 | Notebook |
| Aina | 50.00 | Pen |
| Aina | 50.00 | Stapler |
| Bakri | 30.00 | Notebook |
matched on order_id — one orders row can produce several result rows
Before you run it — orders has 2 rows. How many rows does this join return, and does 50.00 appear more than once?
Now sum it
This is where it stops being cosmetic. Someone asks "what's total revenue?" and you reach for SUM(order_total) on the joined result, the same way you would on the bare orders table.
Before you run it — SUM(order_total) on the bare orders table is 80.00. What does this query return, and is it still 80.00?
Why it happens
A JOIN doesn't attach order_total to the order — it attaches it to every row of the result. Aina's order produces three result rows because she has three items, and order_total gets copied onto all three. SUM then adds 50.00 three times, once per row, because it has no way to know those three 50.00s are the same order repeated. The join didn't do anything wrong; it did exactly what a join does. The mistake is aggregating a one-side column after fanning it out across the many side.
Two fixes, for two different questions
If you actually want per-item detail (which item, which order it belongs to), the fan-out is correct and expected — don't sum order_total in that query at all. If you want a per-order total, aggregate before joining, or aggregate the many-side separately and join back to the one-side once. Here we sum item counts per order first, then join to the untouched orders table.
Before you run it — does order_total get duplicated in this version? How many rows come back?
The habit that catches this before it ships
Before trusting any total computed after a join, run one sanity check: SELECT COUNT(*) FROM orders versus SELECT COUNT(*) FROM orders JOIN order_items ON .... If the second number is bigger than the first, at least one order matched more than one item row, and every one-side column in that result is now duplicated. This single comparison is worth running by habit on every join before you aggregate through it, not just when a number looks suspicious — by the time a number looks wrong, it may already have been reported.
If you've used Excel or Google Sheets
This is the SQL version of a VLOOKUP-then-SUM mistake: pulling a per-order total onto a per-item sheet via lookup, then summing the whole sheet — the looked-up value repeats on every matching row, exactly like order_total here, and a spreadsheet won't warn you either.
Ready to practice? The questions below are dedup and fan-out shaped: Ninja Van's double-bucketed experiment users is the simplest version of "a row appearing more than once corrupts a count."
The rule to keep: after any JOIN, ask whether the row count could have grown, and if a one-side value is about to be aggregated, aggregate the many-side down to one row per key first. Next: Dates that quietly lose a day.