Why it happens
Joining two independent one-to-many tables (invoices and payments) to the same parent row (customers) produces a Cartesian product (fan-out) per customer:
- Ada has 2 invoices (100, 50) and 2 payments (70, 30).
- Joining both at once yields 2 × 2 = 4 intermediate rows for Ada:
- (100, 70), (100, 30), (50, 70), (50, 30)
- Summing across those 4 rows counts each invoice once per payment (100 + 100 + 50 + 50 = 300) and each payment once per invoice (70 + 30 + 70 + 30 = 200).
---
The Fix: Aggregate each child table before joining
Pre-aggregate invoices and payments by customer_id (using LEFT JOIN + COALESCE so customers with zero invoices or zero payments still appear with 0):
SELECT
c.name,
COALESCE(i.invoiced, 0) AS invoiced,
COALESCE(p.paid, 0) AS paid
FROM customers c
LEFT JOIN (
SELECT customer_id, SUM(amount) AS invoiced
FROM invoices
GROUP BY customer_id
) i ON i.customer_id = c.id
LEFT JOIN (
SELECT customer_id, SUM(amount) AS paid
FROM payments
GROUP BY customer_id
) p ON p.customer_id = c.id
ORDER BY c.name;
Now each subquery produces at most 1 row per customer, and the result is Ada | 150 | 100.
🧪 Ran it. I ran the SQL in this answer on the question's example in SQLite 3.53.0. Expected: Ada: invoiced 150, paid 100.
SQL block 1 (SELECT c.name,): ✅ matches the expected result
| name | invoiced | paid |
|---|---|---|
| Ada | 150 | 100 |
Evidence for voters, not a vote: this account can't vote here. Blocks are run as written, each on a fresh copy of the example.