Free practical guide

SQL JOINs: why totals double and how to fix them

A join can be syntactically correct and still give a wrong business answer. If you join an invoice to multiple invoice lines, its total may be counted multiple times. The fix starts with understanding what one row represents.

The mistake: one invoice becomes two rows

Imagine two invoices: INV-01 for €100 and INV-02 for €200. The first invoice has two lines; the second has one. Their real combined value is €300.

Join each invoice to every matching line and INV-01 appears twice. Summing the invoice-level amount after that join produces €100 + €100 + €200 = €400, even though the original invoices total €300.

WITH invoices(invoice_id, net_amount) AS (
  VALUES ('INV-01', 100), ('INV-02', 200)
), invoice_lines(invoice_id, line_id) AS (
  VALUES ('INV-01', 1), ('INV-01', 2), ('INV-02', 1)
)
SELECT SUM(i.net_amount) AS total_after_join
FROM invoices AS i
JOIN invoice_lines AS l
  ON i.invoice_id = l.invoice_id;

The result is 400. The SQL ran successfully, but the meaning of a row changed when the tables were combined.

Fix it at the right grain

If the question is the total value of invoices, calculate it from the invoice table, where every invoice appears once:

WITH invoices(invoice_id, net_amount) AS (
  VALUES ('INV-01', 100), ('INV-02', 200)
)
SELECT SUM(net_amount) AS invoiced_total
FROM invoices;

Result: 300. If you need to include only invoices that have at least one line, use EXISTS rather than creating duplicate rows:

WITH invoices(invoice_id, net_amount) AS (
  VALUES ('INV-01', 100), ('INV-02', 200)
), invoice_lines(invoice_id, line_id) AS (
  VALUES ('INV-01', 1), ('INV-01', 2), ('INV-02', 1)
)
SELECT SUM(i.net_amount) AS invoiced_total
FROM invoices AS i
WHERE EXISTS (
  SELECT 1 FROM invoice_lines AS l
  WHERE l.invoice_id = i.invoice_id
);

Result: 300. If instead you need totals by line category, aggregate the line-level amounts at the appropriate level first. Do not use SUM(DISTINCT i.net_amount) to hide duplicates: two genuinely different invoices can have identical amounts.

Try the same idea on business data

SQL for Analysis, Module 5 covers INNER JOIN, LEFT JOIN and the double-counting mistake using realistic Raket Parts data. You can also open the free SQL editor to experiment with the queries above. Both use in-browser SQL with no installation.

For the official SQL query semantics, see the SQLite SELECT documentation.