Skip to course content
Free SQL course

SQL for Data Analysis and AI

Unit 01.02: The fan-out trap: when a join doubles your money

Unit ID: SQL-M01-U03 - Estimated active time: 16-20 minutes Objective: recognise and fix join fan-out, the most expensive silent error in analytics SQL.

The bug that passes review

Fan-out: one order total counted three times after a join After joining orders to order_items, order 501 appears on three rows and its order_total of 1000.00 rupees is repeated on each one. Summing order_total across the joined result returns 3000.00 for a single order worth 1000.00. Across the whole table this turns true revenue of 27,01,463 rupees into 91,47,789 rupees, roughly 3.4 times too large. The fix is to aggregate order_items to one row per order first, then join. The same money, counted once per line item Result of joining orders to order_items, for order 501 only orders JOIN order_items order_id sku order_total 501 SKU-A 1000.00 501 SKU-B 1000.00 501 SKU-C 1000.00 the order value is repeated, not split SUM(order_total) returns 3000.00 The order is actually worth 1000.00 Across all 1,000 orders this reports revenue of 91,47,789 instead of 27,01,463 No error is raised. Only the number is wrong. The check: compare COUNT(*) before and after the join. The fix: aggregate order_items to one row per order, then join that.
The order value is repeated once per line item, so SUM counts it three times.

This query looks completely reasonable:

SELECT SUM(o.order_total) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id;

It is wrong, and it will not error. It will return a number roughly three times too large.

Why: joining orders (one row per order) to order_items (many rows per order) duplicates each order row once per item. An order worth ₹1,000 with 3 items now contributes ₹3,000. This is fan-out.

See it happen

-- Order 501 has one row here
SELECT order_id, order_total FROM orders WHERE order_id = 501;
-- 501 | 1000

-- ...and three rows here
SELECT order_id, sku FROM order_items WHERE order_id = 501;
-- 501 | A
-- 501 | B
-- 501 | C

-- So the join produces three rows, each carrying order_total = 1000
SELECT o.order_id, o.order_total, i.sku
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.order_id = 501;
-- 501 | 1000 | A
-- 501 | 1000 | B
-- 501 | 1000 | C
-- SUM(order_total) = 3000, not 1000

Three ways to fix it

1. Do not join at all. If the number lives in orders, read it from orders:

SELECT SUM(order_total) AS revenue FROM orders;

2. Aggregate the many-side first, then join.

SELECT SUM(o.order_total) 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;

3. Aggregate a distinct measure. Only safe when the value is genuinely per-order:

-- fragment: illustrative shape, not a runnable query
SELECT SUM(DISTINCT ...) -- almost always the wrong instinct; prefer options 1 and 2

DISTINCT is a trap here: two different orders may legitimately have the same total, and SUM(DISTINCT) would drop one of them. Reach for it last, if ever.

The habit that catches fan-out every time

Count rows before and after the join:

SELECT COUNT(*) FROM orders;                      -- 1,000
SELECT COUNT(*) FROM orders o
JOIN order_items i ON i.order_id = o.order_id;    -- 3,400  <-- grain changed

If the count changed and you did not intend it to, stop. Do not aggregate anything until you understand why.

Practice

A colleague reports monthly revenue jumped 40% with no change in orders. Their query joins orders to payments (a customer may pay an invoice in instalments).

  1. What is the likely cause?
  2. Write the row-count check that would confirm it.
  3. Give a fix that keeps the payment information.
Check your answer
  1. Fan-out. Orders paid in two instalments appear twice, doubling order_total for those orders.
  2. SELECT COUNT(*) FROM orders; vs the same count with the join - if the second is larger, fan-out is present.
  3. Aggregate payments first:
SELECT SUM(o.order_total) AS revenue, SUM(p.paid) AS collected
FROM orders o
LEFT JOIN (SELECT order_id, SUM(amount) AS paid FROM payments GROUP BY order_id) p
  ON p.order_id = o.order_id;

Takeaway

A join that changes the row count has changed the grain. Aggregating after an unintended grain change is how confident, well-formatted, completely wrong numbers reach a slide.

---