← All Kits · SQL Kit

Why Your JOIN Duplicated the Rows and the Total Is Too High

Michael Nocito · Updated August 2026 · Every query on this page was run before it was published

The query ran, the numbers came back, and revenue is higher than finance says it should be. Nothing errored. Somewhere in the query a join matched one row against several, and every value on the one side got counted more than once.

What you do: count the rows before and after the join. If the count grew, you have fan-out, and every sum in the query is wrong.

The short version. A join multiplies rows whenever the key repeats on the other side.

The two tables

CREATE TABLE orders(order_id INT, customer_id INT, amount INT, status TEXT);
INSERT INTO orders VALUES
(101,1,120,'shipped'),(102,1,80,'shipped'),(103,2,200,'cancelled'),
(104,2,50,'shipped'),(105,3,300,'shipped'),(106,4,90,'pending'),
(107,4,60,'shipped'),(108,5,150,'cancelled');

CREATE TABLE payments(payment_id INT, order_id INT, amount INT);
INSERT INTO payments VALUES
(1,101,60),(2,101,60),(3,102,80),(4,104,50),(5,105,300),(6,107,60);

Eight orders totalling 1,050. Six payments. Order 101 was paid in two instalments of 60, which is the only unusual thing in the data and it is enough.

The query that goes wrong

SELECT COUNT(*), SUM(o.amount)
FROM orders o
JOIN payments p ON p.order_id = o.order_id;
-- 6 rows, 730

Five orders have payments: 101, 102, 104, 105 and 107. Their true total is 120 + 80 + 50 + 300 + 60 = 610. The query says 730, because order 101 appears twice and contributes its 120 twice.

Six rows for five orders. That single extra row is the whole error, and 730 is a plausible enough number that nobody questions it until it is compared with something.

Detecting it in ten seconds

SELECT COUNT(*) FROM orders;                        -- 8
SELECT COUNT(*) FROM orders o
JOIN payments p ON p.order_id = o.order_id;         -- 6, from 5 orders

SELECT order_id, COUNT(*) FROM payments
GROUP BY order_id HAVING COUNT(*) > 1;              -- 101, 2

The last query is the one to keep. It names the key that repeats, which turns "the numbers are too high" into "order 101 has two payment rows", and that is a sentence somebody can act on.

The three correct fixes

1. Aggregate the many side first

SELECT o.order_id, o.amount, COALESCE(p.paid, 0) AS paid
FROM orders o
LEFT JOIN (
    SELECT order_id, SUM(amount) AS paid
    FROM payments
    GROUP BY order_id
) p ON p.order_id = o.order_id;

The subquery returns one row per order, so the join cannot multiply anything. Order 101 arrives as a single row with 120 paid. This is the default answer and the one to reach for first.

2. Use EXISTS when you only need to know whether a match exists

SELECT COUNT(*), SUM(amount)
FROM orders o
WHERE EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.order_id);
-- 5 rows, 610

Five orders, 610. EXISTS asks a yes or no question, so it cannot duplicate a row however many payments there are. If you are joining only to filter, this is both correct and usually faster.

3. Aggregate with the right function

SELECT COUNT(DISTINCT o.order_id) AS orders,
       SUM(p.amount)               AS paid
FROM orders o
JOIN payments p ON p.order_id = o.order_id;

Sum the column from the many side, which has one row per payment and is therefore not duplicated, and count distinct on the one side. Mixing them up is where this fix goes wrong: SUM(o.amount) is still 730 no matter what you count.

Why DISTINCT is not the fix

SELECT SUM(DISTINCT o.amount) FROM ...   -- wrong, and quietly so

SELECT DISTINCT removes duplicate rows, which looks like it solves the problem and does not. Two different orders of exactly 60 are collapsed into one, so the total is now too low. And it cannot help at all when the duplicated rows differ in any column, which they usually do once you select a few more fields.

Reaching for DISTINCT to fix a total is the clearest sign that the grain of the query has not been decided. Decide it instead: one row per order, or one row per payment, and build to that.

The habit that prevents it

How to apply this to your own work

1. Take a query you rely on and count the rows after each join. Add them as a comment in the query.
2. Run the duplicate-key check on every table you join to. Uniqueness is an assumption until it is tested.
3. Replace any DISTINCT that was added to fix a total with a pre-aggregated subquery.
4. Where a join exists only to filter, rewrite it as EXISTS.
5. Reconcile the total against a number from another system, which is the only check that catches fan-out you did not think to look for.

The one habit to keep

Say what one row of the result represents before you write the FROM. Fan-out is not really a join problem; it is what happens when a query is written without deciding its grain.

Does the report you send most often join to a table whose key you have never tested for uniqueness?