Why Is My SQL SUM() Wrong After a JOIN?

Live example: the exact multiplier

Same schema as always — customers, orders, order_items — running as a real SQLite database in your browser. Marcus Johnson has one order worth $500 and three line items on it. Run the buggy query and watch his revenue come back as $1,500 — exactly 500 × 3, because the JOIN produced three rows for his one order and SUM() summed all three.

That "× 3" isn't a coincidence — it's the mechanism. The multiplier on any given group is always exactly the number of child rows the JOIN produced for that key. Alice Chen has two orders with two and one line items respectively, so her multiplier isn't a single clean number — which is exactly why her wrong total ($680) doesn't look as obviously broken as Marcus's.

Fix 1: pre-aggregate before the JOIN (recommended)

Collapse the many-side table down to one row per join key in a CTE first, so by the time it reaches the JOIN there's nothing left to fan out:

WITH order_item_counts AS (
  SELECT order_id, COUNT(*) AS item_count
  FROM order_items
  GROUP BY order_id
)
SELECT
  c.name AS customer_name,
  SUM(o.total_amount) AS revenue,
  SUM(oic.item_count) AS total_items
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_item_counts oic ON oic.order_id = o.id
GROUP BY c.name;

This is the most robust fix because it works regardless of how many child rows exist per key, and it still lets you report on the child table (here, total_items) without corrupting anything from the parent table. See SQL Recursive CTEs With Examples for more on structuring multi-step CTEs like this one.

Fix 2: SUM(DISTINCT ...) — why it's a trap, not a fix

A common instinct is to reach for SUM(DISTINCT o.total_amount), and on small test data it often appears to work — which is exactly what makes it dangerous. DISTINCT deduplicates by value, not by row, so it silently drops legitimate revenue the moment two different orders happen to share the same total:

-- Looks like a fix, but is only correct by accident:
SELECT c.name AS customer_name, SUM(DISTINCT o.total_amount) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.name;
-- Breaks the moment one customer places two DIFFERENT orders
-- that happen to total the exact same amount — one gets silently dropped.

Use SUM(DISTINCT ...) only when you specifically need distinct values summed — never as a general-purpose fix for a fan-out join, where it trades an overcounting bug for a harder-to-spot undercounting one.

Fix 3: filter to one row per key with a window function

When you need to keep row-level detail from the many-side table alongside the parent's value, rank rows with ROW_NUMBER() and filter to one per key before summing:

WITH ranked AS (
  SELECT o.*, oi.id AS item_id,
         ROW_NUMBER() OVER (PARTITION BY o.id ORDER BY oi.id) AS rn
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
)
SELECT customer_id, SUM(total_amount) AS revenue
FROM ranked
WHERE rn = 1   -- exactly one row per order survives
GROUP BY customer_id;

This is more verbose than pre-aggregating, but it's useful when the query already needs ROW_NUMBER() for something else and adding a second CTE would be redundant. See Window Functions Explained Simply if OVER (PARTITION BY ...) is unfamiliar.

AVG() has the identical problem — and it's harder to catch

Every fix above applies equally to AVG(), but the bug itself is more dangerous there. SUM() inflating to an obviously-too-large number tends to get questioned. AVG() shifting toward whichever rows got duplicated the most can still land on a perfectly plausible-looking number:

Alice's average shifts by about 5% — nowhere near as alarming as Marcus's SUM tripling, which is exactly why AVG-based dashboards are more likely to ship a fan-out bug into production undetected. The fix is identical: pre-aggregate the many-side table before the JOIN, regardless of which aggregate function sits on top of it.

Common mistakes

• Reaching for SUM(DISTINCT ...) as a default fix instead of diagnosing whether a JOIN is fanning out — it trades one bug for a quieter one.
• Only checking SUM() and assuming COUNT() and AVG() are fine — every aggregate function fed by the same fanned-out rows is affected identically.
• Testing against tiny sample data where every order happens to have exactly one line item, which hides fan-out completely until real data with multi-item orders hits production.
• Fixing the symptom on the outer query (e.g. dividing by a row count) instead of fixing the JOIN that caused the duplication in the first place.

Challenge: fix it yourself

Same live editor above, three new tasks — including one that checks whether you can spot the AVG() version of this bug, not just the SUM() version.