join fan-out (why your totals are wrong)

This is probably the most common bug in real sql, and it doesn't give an error. The query runs, you get a number, the number is wrong. Usually too big.

what's happening

When you join a table where one row matches many rows, the one row gets copied once per match. That's normal. The problem is when you then SUM or COUNT something from the "one" side, you're summing the copies.

see it happen

Neha (customer 6) has 1 order of 4298, and 3 addresses.

SELECT c.first_name, o.order_id, o.amount, a.city
FROM customers c
JOIN orders    o ON c.customer_id = o.customer_id
JOIN addresses a ON c.customer_id = a.customer_id
WHERE c.customer_id = 6;

first_name	order_id	amount	city
Neha	108	4298	Mumbai
Neha	108	4298	Pune
Neha	108	4298	Mumbai

Her one order is now 3 rows. Now sum it:

SELECT c.first_name, SUM(o.amount) AS total_spent, COUNT(a.address_id) AS addresses
FROM customers c
JOIN orders    o ON c.customer_id = o.customer_id
JOIN addresses a ON c.customer_id = a.customer_id
GROUP BY c.customer_id, c.first_name;

Neha shows 12894 spent. She spent 4298. Aman has 3 orders and 2 addresses, so his orders get doubled and his address count gets tripled. Everything's wrong.

why it happens

You joined two different "many" tables (orders and addresses) to the same "one" table (customers). They have nothing to do with each other, but the join pairs every order with every address. 3 orders × 2 addresses = 6 rows for Aman.

how to spot it

• the total looks too big
• quick check, COUNT(*) before and after adding a join. If it went up and you didn't expect it, fan-out.
• someone added DISTINCT "to fix duplicates". Big hint.

fix 1: aggregate before joining

Squash each "many" table down to one row per customer first, then join the squashed versions.

WITH spend AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
),
addr AS (
  SELECT customer_id, COUNT(*) AS addresses
  FROM addresses
  GROUP BY customer_id
)
SELECT c.first_name,
       COALESCE(s.total_spent, 0) AS total_spent,
       COALESCE(a.addresses, 0)   AS addresses
FROM customers c
LEFT JOIN spend s ON c.customer_id = s.customer_id
LEFT JOIN addr  a ON c.customer_id = a.customer_id;

Now each join is one-to-one, nothing multiplies. This is the proper fix, use it.

fix 2: scalar subqueries

SELECT c.first_name,
  (SELECT SUM(amount) FROM orders o    WHERE o.customer_id = c.customer_id) AS total_spent,
  (SELECT COUNT(*)    FROM addresses a WHERE a.customer_id = c.customer_id) AS addresses
FROM customers c;

Works fine, readable for small stuff.

the not-really-a-fix: COUNT DISTINCT

COUNT(DISTINCT o.order_id) gives the right order count even with fan-out. But SUM(DISTINCT o.amount) is wrong, if two different orders had the same amount it'd only count one. Don't use SUM DISTINCT to fix fan-out.

the same thing with order_items

-- wrong: order 101 has 2 items so its amount is counted twice
SELECT SUM(o.amount)
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;

Gives 29,498 instead of 23,082. Either don't join order_items if you don't need it, or sum from the item level (quantity * price) instead of the order level.

the rule

Before you SUM or COUNT, ask "what is one row in this result?". If the answer is "one order-address pair" but you're summing order amounts, you've got fan-out.

practice

Check queries.sql.

-- join fan-out
-- run sample-data.sql first

-- examples ---------------------------------------------------------

-- Neha's one order shows up 3 times
SELECT c.first_name, o.order_id, o.amount, a.city
FROM customers c
JOIN orders    o ON c.customer_id = o.customer_id
JOIN addresses a ON c.customer_id = a.customer_id
WHERE c.customer_id = 6;

-- wrong totals
SELECT c.first_name, SUM(o.amount) AS total_spent, COUNT(a.address_id) AS addresses
FROM customers c
JOIN orders    o ON c.customer_id = o.customer_id
JOIN addresses a ON c.customer_id = a.customer_id
GROUP BY c.customer_id, c.first_name;

-- right totals
WITH spend AS (
  SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id
),
addr AS (
  SELECT customer_id, COUNT(*) AS addresses FROM addresses GROUP BY customer_id
)
SELECT c.first_name, COALESCE(s.total_spent, 0) AS total_spent, COALESCE(a.addresses, 0) AS addresses
FROM customers c
LEFT JOIN spend s ON c.customer_id = s.customer_id
LEFT JOIN addr  a ON c.customer_id = a.customer_id;


-- practice ---------------------------------------------------------

-- 1. run this and explain why it's wrong:
--    SELECT SUM(o.amount) FROM orders o JOIN order_items oi ON o.order_id = oi.order_id;
-- 2. per customer: number of orders and number of items bought (sum of quantity). do it without fan-out.
-- 3. per customer: total spent and number of distinct cities in their addresses
-- 4. per order: amount, number of line items. check that the amount is not repeated.


-- answers ----------------------------------------------------------

-- 1
-- 29498 instead of 23082. orders with 2 items (101, 105, 108, 110) get their amount added twice.
SELECT SUM(amount) FROM orders;  -- the real total

-- 2
WITH order_counts AS (
  SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id
),
item_counts AS (
  SELECT o.customer_id, SUM(oi.quantity) AS items
  FROM orders o JOIN order_items oi ON o.order_id = oi.order_id
  GROUP BY o.customer_id
)
SELECT c.first_name, COALESCE(oc.orders, 0) AS orders, COALESCE(ic.items, 0) AS items
FROM customers c
LEFT JOIN order_counts oc ON c.customer_id = oc.customer_id
LEFT JOIN item_counts  ic ON c.customer_id = ic.customer_id;

-- 3
SELECT c.first_name,
  (SELECT COALESCE(SUM(amount), 0) FROM orders o WHERE o.customer_id = c.customer_id) AS spent,
  (SELECT COUNT(DISTINCT city) FROM addresses a WHERE a.customer_id = c.customer_id) AS cities
FROM customers c;

-- 4
SELECT o.order_id, o.amount, COUNT(oi.product_id) AS line_items
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.amount;
-- grouping by order_id puts the copies back into one row, so amount isn't summed here