Skip to main content
← Check a sales report and a latency dashboardStep 1 of 2

Databases & SQL

Why two correct SQL joins can double a total

Reconcile sales and refunds for a shop. Use a two-line receipt to expose duplicated amounts and repair the report.

Try the 5-minute exercise

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

Step 1 of 2 · Check a sales report and a latency dashboard

Put this step into practice.

Reconcile sales and refunds for a shop. Use a two-line receipt to expose duplicated amounts and repair the report.

This records your own practice. Try checked exercises on your Today page →

Enable JavaScript to save progress. You can still continue through the path.

Put it into practice · 5 minutes

Count rows before summing a receipt

Try the exercise and get feedback on your answer.

Try the exercise