Revenue doubled after joining orders to a one-to-many shipments table. Diagnose and fix.
Total revenue was correct until this report joined orders to shipments (one order can have many shipments). Now total is about double the real figure.
SELECT o.region, SUM(o.amount) AS total
FROM orders o
JOIN shipments s ON s.order_id = o.order_id
GROUP BY o.region;Explain why, and fix the aggregate so revenue is right.
The one-to-many shipments join repeats each order row once per shipment, so SUM(o.amount) adds each order's amount multiple times — join fan-out. Aggregate shipments to one row per order first (a subquery or CTE), then join, or only SUM a shipment-grain value.
- ✗Summing a parent column across a one-to-many child join
- ✗Blaming a cross join instead of legitimate fan-out
- ✗Trying to average away the duplicated rows
- →How would COUNT(DISTINCT o.order_id) help detect fan-out?
- →When is pre-aggregating shipments better than SUM(DISTINCT)?
Each order with N shipments becomes N rows after the join, so SUM(o.amount) adds that order's amount N times. With most orders shipped twice, the total roughly doubles. This is join fan-out — a real duplication, not a wrong keyword.
Collapse the child table to one row per order before joining (or skip the join entirely if you only need order-level revenue):
SELECT o.region, SUM(o.amount) AS total
FROM orders o
JOIN (
SELECT order_id
FROM shipments
GROUP BY order_id -- one row per order
) s ON s.order_id = o.order_id
GROUP BY o.region;If you must keep the row expansion (e.g. you also report a shipment metric), count the parent value once with SUM(o.amount) FILTER (WHERE s.shipment_seq = 1) or use COUNT(DISTINCT o.order_id) to verify no order is double-counted.
