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

  1. Name the grain of the result ("one row per customer") and of each table.
  2. Every one-to-many join below that grain needs pre-aggregation.
  3. Count rows per key after joins before summing.
  4. LEFT JOIN the pre-aggregated CTEs (so parents with no children survive) and wrap presented sums in COALESCE(..., 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.