Fan-Out: When Joins Double-Count

Karim in Finance sends a quick one: “For 2025, by channel, how much did we collect in shipping fees, and how much item revenue?” You already know the join. Here’s the query most people write first:

SELECT
  o.channel,
  ROUND(SUM(o.shipping_fee), 2) AS shipping,
  ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount)), 2) AS item_revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
  AND o.order_date >= '2025-01-01'
  AND o.order_date <  '2026-01-01'
GROUP BY o.channel;

Item revenue is right. Shipping is about double what it should be: web shows 6,732 when the real figure is 3,413. Nothing errored, and nothing looks absurd at a glance. This is fan-out, the most expensive bug in analytics, because it produces believable numbers.

Where the extra money comes from

orders and order_items have a one-to-many relationship: one order, one or more lines. When you join them, the result has one row per line, and each line carries a copy of its order’s columns, shipping_fee included.

Item revenue survived because it lives on the many side: each line’s revenue appears exactly once. Shipping lives on the one side, so it’s counted once per line. Cartwheel orders average 1.93 lines, which is why shipping roughly doubled.

The general rule: a join changes the grain to the grain of its most detailed table, and any number stored at a coarser grain gets repeated.

The fix: aggregate first, then join

Bring each table to the grain of the answer before joining. Here, collapse order_items to one row per order in a subquery, then join that to orders. Now both sides have one row per order, and nothing repeats:

SELECT
  o.channel,
  COUNT(*) AS orders,
  ROUND(SUM(o.shipping_fee), 2) AS shipping,
  ROUND(SUM(i.revenue), 2) AS item_revenue
FROM orders AS o
JOIN (
  SELECT order_id, SUM(quantity * unit_price * (1 - discount)) AS revenue
  FROM order_items
  GROUP BY order_id
) AS i ON i.order_id = o.order_id
WHERE o.status <> 'cancelled'
  AND o.order_date >= '2025-01-01'
  AND o.order_date <  '2026-01-01'
GROUP BY o.channel;

A subquery in FROM (a derived table) behaves like a temporary table with the grain you chose, one row per order_id here. Shipping now matches a query on orders alone, item revenue is unchanged, and COUNT(*) counts orders, because the rows are orders again. The next section shows a tidier way to write the same thing with WITH.

Two fans at once

Fan-out compounds. Julia asks how much revenue came from orders that needed a support ticket. Join items and tickets to the same orders, and each line is repeated once per ticket on its order; 110 orders have more than one ticket. The naive sum says 166,562. The right answer, which asks whether a ticket exists rather than joining every ticket, is 140,675:

SELECT ROUND(SUM(quantity * unit_price * (1 - discount)), 2) AS revenue_with_tickets
FROM order_items
WHERE order_id IN (SELECT order_id FROM support_tickets);

IN (subquery) filters without joining, so it can never multiply rows. This “does a match exist?” pattern, a semi-join, gets a full treatment in the next lesson along with its opposite.

Spotting a fan-out before it bites

You don’t have to wait for a wrong total. Before joining, ask of each join key: is it unique on this side? order_id is unique in orders (it’s the primary key) but not in order_items or support_tickets, so joining either one to orders multiplies order rows. A quick probe tells you:

SELECT
  COUNT(*)                 AS rows_after_join,
  COUNT(DISTINCT order_id) AS distinct_orders
FROM order_items;

3,991 rows against 2,066 orders: a fan-out factor of nearly two. When those two numbers differ, any column you sum from the “one” side needs to be pre-aggregated or left out of the join. When they’re equal, the join is one-to-one and safe for sums from either side.

The reconciliation habit

Every time you join, reconcile at least one total against a single-table version. Shipping by channel from the joined query should equal shipping by channel from orders alone; item revenue should equal the sum over order_items for the same orders. If they match, the join didn’t fan out. If they don’t, you’ve caught the bug before Karim’s board deck did.

Next you’ll join tables to themselves and look for rows that have no match at all.