SQL JOIN Fan-Out: What It Is, Why SUM Breaks & 2 Proven Fixes

This is the single most dangerous SQL bug in analytics work. It does not crash your query. It does not throw a warning. It produces a number that looks completely reasonable — and is off by over a million dollars.

Every analyst who has joined tables and then aggregated has hit this trap. It is a classic interview question and a classic take-home failure. This guide walks through exactly how it happens, why, and the two fixes that prevent it — using a real e-commerce dataset where every number is verified against a live PostgreSQL database. For related SQL concepts, see our SQL NULL Guide and SQL CTE Guide.

1. How Joins Chain Across Tables

Before the trap, the mechanics. Joins chain: orders connect to order_items, and order_items connect to products. Three tables, two JOINs.

SELECT o.order_id,
       p.product_name,
       p.category,
       oi.quantity,
       oi.unit_price
FROM orders AS o
JOIN order_items AS oi ON o.order_id    = oi.order_id
JOIN products    AS p  ON oi.product_id = p.product_id
LIMIT 10;

Each JOIN ... ON adds another table along its key. Now you can see every product inside every order. But notice what just happened to the grain of the data: one order can span several rows here — one per line item. That innocent fact is the whole trap.

The Table Relationships

orders (1) ──< order_items (many)    via order_id     [enforced FK]
products (1) ──< order_items (many)   via product_id   [enforced FK]

One order has many line items. One product appears in many line items. When you join orders to order_items, each order row is duplicated once per line item it contains.

2. The Trap, Live

Here is the true total order revenue, straight from the orders table:

SELECT ROUND(SUM(total_amount), 2) AS revenue
FROM orders;

Result: 782,905.04

Now something that looks completely innocent — join in order_items (maybe to filter by product later), and sum the same column:

SELECT ROUND(SUM(o.total_amount), 2) AS revenue
FROM orders AS o
JOIN order_items AS oi ON o.order_id = oi.order_id;

Result: 2,009,577.61

The same SUM, of the same column, on the same orders. It ballooned 2.57 times, just because of one join. No error. It just hands you a wrong answer. If you pasted that into a board deck, you would have overstated revenue by over 1.2 million dollars.

3. Why It Happens: The Grain Change

Here is the mechanism. An order with several line items does not stay one row after the join. It becomes one row per line item, and total_amount is copied onto every one of them.

Proof: One Order, Repeated

SELECT o.order_id, o.total_amount, oi.item_id
FROM orders AS o
JOIN order_items AS oi ON o.order_id = oi.order_id
WHERE o.order_id = 1;

Order 1 has 5 line items. After the join, it occupies 5 rows — and total_amount (249.99) is copied onto every single one. When you SUM(total_amount), you count that order's total 5 times instead of once.

The orders "fanned out" across their line items, and the sum counted the duplicates. Any time you join a one-to-many relationship and then aggregate a value from the "one" side, you risk this.

4. Fix 1: Pre-Aggregate Before Joining

The real fix is to stop summing the order total across line items. Aggregate the orders first, ensuring one row per order, then join.

-- The fix: pre-aggregate orders to one row each, THEN join
SELECT ROUND(SUM(o.total_amount), 2) AS revenue
FROM (SELECT order_id, total_amount FROM orders) AS o
JOIN (SELECT DISTINCT order_id FROM order_items) AS oi
  ON o.order_id = oi.order_id;

Result: 782,905.04 — back to the true order revenue.

The subquery SELECT DISTINCT order_id FROM order_items ensures each order appears only once. The orders table already has one row per order, so joining against a deduplicated list of order IDs preserves that grain. The total cannot fan out because there is no duplication.

The Rule of Thumb

Before you SUM after a join, ask: what is one row here, and am I double-counting?

If one row in your joined result represents something smaller than what you are summing (e.g., one row = one line item, but you are summing an order-level total), you have a fan-out problem.

5. Fix 2: COUNT(DISTINCT) for Headcounts

When you need a count rather than a sum, COUNT(DISTINCT order_id) sidesteps the duplication entirely:

SELECT COUNT(DISTINCT o.order_id) AS unique_orders
FROM orders AS o
JOIN order_items AS oi ON o.order_id = oi.order_id;

Even though each order appears multiple times (once per line item), COUNT(DISTINCT) collapses them back to one. This is safe for headcounts, order counts, customer counts — any metric where you need the number of unique entities.

6. The Three Numbers: Don't Confuse Them

There are three numbers people reach for after joining orders to order_items. Only one is "order revenue."

Gross Line-Item Value Is Not the Fix

-- A DIFFERENT metric: gross line-item value, not order revenue
SELECT ROUND(SUM(oi.quantity * oi.unit_price), 2) AS line_value
FROM orders AS o
JOIN order_items AS oi ON o.order_id = oi.order_id;

Result: 2,731,234.13

This 2.7M is not the fan-out artifact, and it is not wrong — it just answers a different question. It is gross line-item value, not order revenue. And it has two caveats:

1. It silently drops 169 line items with a NULL quantity (NULL * anything = NULL, which SUM skips).
2. It ignores discounts (discount_percent exists on order_items but is not factored in).

So it is gross, not net. Three numbers, then: the 783K order total, the 2.0M fan-out artifact (which is garbage), and the 2.7M gross item value (which is a different metric). Only one of them is "the order revenue."

7. The Fan-Out Checklist

Before trusting any SUM or AVG after a join, run through this checklist:

1. Identify the grain. What does one row represent in your joined result? If it is smaller than what you are aggregating, you have a problem.
2. Check for one-to-many joins. Did you join a table that has multiple rows per key on the "one" side? That is where fan-out lives.
3. Run a proof query. SELECT order_id, total_amount, item_id — if total_amount is repeated per item_id, you have duplication.
4. Pre-aggregate or use DISTINCT. Roll the "one" side to one row per entity before joining, or use COUNT(DISTINCT) for headcounts.
5. Compare to the unjoined total. If SUM before the join differs from SUM after the join, fan-out is the likely cause.

8. Fan-Out Beyond SUM: AVG Is Worse

SUM is the obvious victim, but AVG is more insidious because it does not just inflate — it reweights.

If an order with a large total_amount happens to have many line items, that order's total gets counted more times in the average. The average is no longer "the average order" — it is "the average line-item-weighted order," which is a meaningless metric.

-- This AVG is wrong — orders with more line items are weighted more heavily
SELECT AVG(o.total_amount) AS avg_order
FROM orders AS o
JOIN order_items AS oi ON o.order_id = oi.order_id;

The fix is the same: pre-aggregate to one row per order before computing the average.

9. Practice Fan-Out on Real Databases

Recognizing fan-out is a skill. Fixing it under interview pressure requires practice. The best way to internalize it is to break a few SUMs on purpose and watch the numbers inflate.