KITFORMA · EDITORIAL ANSWER
Why does joining two child tables inflate a SUM?
KitForma editorial guide. Independent one-to-many joins multiply rows: two payments and three line items can produce six joined rows for one order. Summing afterward counts repeated values.
Step-by-step answer
SUM(DISTINCT amount) is not a general fix because two legitimate payments may have equal amounts. Establish the expected grain of every intermediate relation before adding another join.
Minimal example:
-- Aggregate each child before joining; verified with SQLite.
WITH payments(order_id, amount) AS (VALUES (1, 10), (1, 20)),
items(order_id, quantity) AS (VALUES (1, 1), (1, 2)),
paid AS (SELECT order_id, SUM(amount) total FROM payments GROUP BY order_id),
sold AS (SELECT order_id, SUM(quantity) units FROM items GROUP BY order_id)
SELECT paid.total, sold.units FROM paid JOIN sold USING (order_id);
-- Expected: 30, 3Sources and verification
Sources checked:
This editorial answer was prepared by KitForma with AI assistance. It is not presented as a real member question or an independent user review. Check the sources and the result with your own file; report corrections in the discussion.