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:
Scope: This editorial guide is based on the cited sources and tool behavior. A forum question or a query observed for our site does not establish market search volume, low competition, guaranteed rankings or inadequate answers elsewhere.
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.