A query runs, returns plausible numbers, and revenue is 4× what finance says. No syntax error, no NULLs, no warning. Nine times out of ten it's fan-out: you joined two one-to-many tables onto the same parent and every parent row got multiplied.
Here's the smallest reproduction I know, runnable in SQLite.
The data
CREATE TABLE orders(order_id INTEGER PRIMARY KEY, customer TEXT, order_total REAL);
CREATE TABLE order_items(item_id INTEGER PRIMARY KEY, order_id INTEGER, sku TEXT, qty INTEGER);
CREATE TABLE payments(payment_id INTEGER PRIMARY KEY, order_id INTEGER, amount REAL);
INSERT INTO orders VALUES (1,'acme',100),(2,'acme',50),(3,'globex',80);
INSERT INTO order_items VALUES (1,1,'A',1),(2,1,'B',2),(3,1,'C',1),(4,2,'A',1),(5,3,'B',1),(6,3,'C',1);
INSERT INTO payments VALUES (1,1,60),(2,1,40),(3,2,50),(4,3,80);
True answer: acme revenue 150, paid 150; globex revenue 80, paid 80.
The bug
SELECT o.customer, SUM(o.order_total) AS revenue, SUM(p.amount) AS paid
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY o.customer;
customer revenue paid
acme 650.0 350.0
globex 160.0 160.0
Why
Order 1 has 3 items and 2 payments. After both joins it appears 3 × 2 = 6 times, so its 100 is summed six times (600) and each payment three times (300). Order 2 has one of each (50 + 50). Order 3 has 2 items × 1 payment, so globex doubles.
The 10-second diagnosis
Right after your joins, count rows per parent key:
SELECT o.order_id, COUNT(*) AS rows_after_join
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY o.order_id;
order_id rows_after_join
1 6
2 1
3 2
Any count above 1 at the grain you're summing means the total is inflated. Make this a habit before trusting any SUM across joins.
The fix: aggregate each child to the parent grain first
WITH items AS (
SELECT order_id, SUM(qty) AS units FROM order_items GROUP BY order_id
),
pay AS (
SELECT order_id, SUM(amount) AS paid FROM payments GROUP BY order_id
)
SELECT o.customer,
SUM(o.order_total) AS revenue,
SUM(items.units) AS units,
SUM(pay.paid) AS paid
FROM orders o
LEFT JOIN items ON items.order_id = o.order_id
LEFT JOIN pay ON pay.order_id = o.order_id
GROUP BY o.customer;
customer revenue units paid
acme 150.0 5 150.0
globex 80.0 2 80.0
Each CTE returns at most one row per order_id, so joining them can't multiply anything. LEFT JOIN keeps orders with no payments yet (an inner join would silently drop them — a different bug with the same "numbers look plausible" symptom).
Things that look like fixes but aren't
-
SUM(DISTINCT o.order_total)— works on this toy data, breaks the day two orders have the same total. -
COUNT(DISTINCT ...)everywhere — fine for counts, useless for sums. - Dividing by the number of items — correct only until a third join is added.
Checklist
- Name the grain of the result ("one row per customer") and of each table.
- Every one-to-many join below that grain needs pre-aggregation.
- Count rows per key after joins before summing.
-
LEFT JOINthe pre-aggregated CTEs (so parents with no children survive) and wrap presented sums inCOALESCE(..., 0).
I made a practice pack of 18 broken-but-runnable queries like this one (fan-out, NULL traps, window-function partitions, date boundaries) on a small SQLite SaaS dataset, each with expected-result CSVs and an explained fix: SQL Query Debugging Practice Pack (pay what you want, from $7). The example above is free to reuse.
Top comments (1)
The 'count rows per parent key after joins' is the single most useful habit in SQL debugging. I've seen people spend hours on a wrong SUM without ever checking the row count. It's a 10-second query that saves hours.