Receipt 1 contains two items worth 8 each. The shop issued refunds of 2 and 3. A report says sales were 32 and refunds were 10. Both tables are correct. The report changed what one row represents before calculating its totals.
Look at the rows before adding them
Joining both child tables by receipt produces four pairs of amounts: (8, 2), (8, 3), (8, 2), (8, 3). Each sale line appears twice. Each refund appears twice. Using SUM(DISTINCT amount) on the sales looks tempting until you notice that two separate items really did cost 8 each. The correct sales total is 16.
Run this in an empty SQLite database. Receipt 2 is a second check on the repair because it has a sale and no refunds.
CREATE TABLE receipts (id INTEGER PRIMARY KEY);
CREATE TABLE sales (receipt_id INTEGER, amount INTEGER);
CREATE TABLE refunds (receipt_id INTEGER, amount INTEGER);
INSERT INTO receipts VALUES (1), (2);
INSERT INTO sales VALUES (1, 8), (1, 8), (2, 7);
INSERT INTO refunds VALUES (1, 2), (1, 3);
Make each side return one row per receipt
WITH sold AS (
SELECT receipt_id, SUM(amount) AS total
FROM sales GROUP BY receipt_id
), returned AS (
SELECT receipt_id, SUM(amount) AS total
FROM refunds GROUP BY receipt_id
)
SELECT r.id, COALESCE(s.total, 0) AS sales_total,
COALESCE(f.total, 0) AS refund_total
FROM receipts AS r
LEFT JOIN sold AS s ON s.receipt_id = r.id
LEFT JOIN returned AS f ON f.receipt_id = r.id
ORDER BY r.id;
The rows are (1, 16, 5) and (2, 7, 0). Neither summary can match a receipt more than once. The left joins preserve receipt 2, and COALESCE renders its absent refund as zero. Check that sales across the report sum to 23, the same as the original sales table.
Try adding a third refund of 2 to receipt 1. Only its refund total should move, from 5 to 7. A sales total that changes when a refund arrives is a strong clue that the joins multiplied your sale lines. If a receipt can carry more than one currency, decide the unit of one report row again before summing amounts. SQLite aggregate functions
