Pitfall
Join fan-out
Joining two one-to-many tables multiplies rows, and sums grow with them.
An order with 3 lines and 2 payments gives 6 rows when you join both: every line is counted twice. Aggregate each child table to the order grain first, then join the results.
The trap
SELECT o.order_id, SUM(l.quantity * l.unit_price) AS ordered FROM orders o JOIN order_lines l ON l.order_id = o.order_id JOIN payments p ON p.order_id = o.order_id WHERE o.order_id = 14 GROUP BY o.order_id;
| order_id | ordered |
|---|---|
| 14 | 235.00 |
The fix
SELECT o.order_id, SUM(l.quantity * l.unit_price) AS ordered FROM orders o JOIN order_lines l ON l.order_id = o.order_id WHERE o.order_id = 14 GROUP BY o.order_id;
| order_id | ordered |
|---|---|
| 14 | 117.50 |