Why do invoice totals grow after joining payments?

Define the decision and input records

I1 has two settled payments. Joining those rows directly to I1 repeats its full invoice amount twice. Summing distinct amounts is also wrong when two different invoices share the same amount. The common table expression reduces payments to one row per invoice before joining at the invoice grain.

All records below are synthetic. Create these tables in a fresh SQLite database before running the read-only query. Quantities are units; monetary values are integer HKD cents, so divide by 100 when presenting HK dollars.

CREATE TABLE customers(id TEXT PRIMARY KEY, name TEXT);
CREATE TABLE invoices(id TEXT PRIMARY KEY, customer_id TEXT, amount_cents INTEGER, due_on TEXT, status TEXT);
CREATE TABLE payments(id TEXT PRIMARY KEY, invoice_id TEXT, amount_cents INTEGER, status TEXT);
CREATE TABLE refunds(id TEXT PRIMARY KEY, payment_id TEXT, amount_cents INTEGER, status TEXT);
INSERT INTO customers VALUES ('C1','Acorn'),('C2','Birch');
INSERT INTO invoices VALUES ('I1','C1',100000,'2026-10-01','issued'),('I2','C2',50000,'2026-10-20','issued'),('I3','C9',20000,'2026-10-02','issued'),('I4','C1',30000,'2026-09-01','void');
INSERT INTO payments VALUES ('P1','I1',40000,'settled'),('P2','I1',20000,'settled'),('P3','I2',50000,'settled'),('P4','I3',20000,'failed');
INSERT INTO refunds VALUES ('R1','P1',10000,'completed'),('R2','P1',5000,'pending'),('R3','P3',50000,'completed');

Run the query and compare the result

WITH paid AS (
 SELECT invoice_id,SUM(amount_cents) AS paid_cents FROM payments
 WHERE status='settled' GROUP BY invoice_id
)
SELECT SUM(i.amount_cents) AS invoice_cents,
       SUM(COALESCE(p.paid_cents,0)) AS gross_paid_cents
FROM invoices i LEFT JOIN paid p ON p.invoice_id=i.id
WHERE i.status='issued';

Issued invoices total 170000 cents (HK$1,700.00); gross settled payments total 110000 cents (HK$1,100.00). A naive join would inflate the invoice total to 270000 cents. Keep invoice value and received cash as separate measures; neither is automatically a recognised-revenue measure.

Apply the result within its limits

Refunds are intentionally excluded from this gross-payment report. It assumes every payment belongs to one invoice; split allocations need an allocation table. Voided invoices and failed payments are excluded explicitly.

The supplied setup and result were executed with SQLite. Adapt the schema and status rules to a test copy of your system, then agree the expected result with the process owner. These examples have not been tested on other database platforms.