Data · SQL
Why SUM doubles after a SQL join
A $30 order shipped in 2 parcels: how many rows does the join of orders and parcels return?
And what does SUM(amount) return on those rows?
From one order in two parcels to a correct total, in five steps:
Why does the same amount show up several times after a join?
A $75 order shipped in 3 parcels comes out 3 times in the join, and each of its 3 rows shows $75.
Let’s take three orders and their parcels.
SELECT orders.id, amount FROM orders JOIN parcels ON parcels.order_id = orders.id
| id | amount |
|---|---|
| 101 | $40 |
| 102 | $25 |
| 103 | $60 |
| id | order_id |
|---|---|
| 501 | 101 |
| 502 | 102 |
| 503 | 102 |
| 504 | 103 |
| orders.id | amount |
|---|---|
| 101 | $40 |
| 102 | $25 |
| 102 | $25 |
| 103 | $60 |
Which parcels carry the number of order 102? Tap them in the table.
Look for 102 in the
order_idcolumn.
SELECT orders.id, amount FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 $35 102 $80 103 $15 104 $50 parcels id order_id 501 101 502 102 503 102 504 102 505 103 506 104 SELECT orders.id, amount FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 $20 102 $70 103 $45 104 $60 parcels id order_id 501 104 502 102 503 101 504 101 505 104 506 104
Why is SUM too high after a join?
If the $75 order comes out 3 times in the join, SUM(amount) adds it 3 times: it counts for $225 in the total instead of $75.
Let’s take the same tables, with SUM.
SELECT SUM(amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
| id | amount |
|---|---|
| 101 | $40 |
| 102 | $25 |
| 103 | $60 |
| id | order_id |
|---|---|
| 501 | 101 |
| 502 | 102 |
| 503 | 102 |
| 504 | 103 |
| orders.id | amount |
|---|---|
| 101 | $40 |
| 102 | $25 |
| 102 | $25 |
| 103 | $60 |
| SUM(amount) |
|---|
| $150 |
SELECT SUM(amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 $50 102 $15 103 $70 parcels id order_id 501 101 502 102 503 102 504 102 505 103 SELECT SUM(amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 $35 102 $80 103 $20 104 $45 parcels id order_id 501 101 502 101 503 102 504 104
How do you count orders without duplicates after a join?
If 12 shipped orders give 20 rows in the join, COUNT(*) returns 20 and COUNT(DISTINCT orders.id) returns 12.
Let’s take three orders, two of them shipped in several parcels.
SELECT COUNT(*),
COUNT(DISTINCT orders.id)
FROM orders
JOIN parcels
ON parcels.order_id = orders.id| id | amount |
|---|---|
| 101 | $40 |
| 102 | $25 |
| 103 | $60 |
| id | order_id |
|---|---|
| 501 | 101 |
| 502 | 101 |
| 503 | 102 |
| 504 | 103 |
| 505 | 103 |
| 506 | 103 |
| orders.id | amount |
|---|---|
| 101 | $40 |
| 101 | $40 |
| 102 | $25 |
| 103 | $60 |
| 103 | $60 |
| 103 | $60 |
| COUNT(*) | COUNT(DISTINCT orders.id) |
|---|---|
| 6 | 3 |
SELECT COUNT(*) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 $15 102 $50 103 $35 104 $70 parcels id order_id 501 101 502 102 503 102 504 102 505 103 506 104 507 104 SELECT COUNT(DISTINCT orders.id) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 $80 102 $20 103 $45 104 $60 parcels id order_id 501 101 502 103 503 101 504 104 505 104
How do you fix a SUM inflated by a join?
Cut the parcels table down to one row per order before the join: the $90 order shipped in 4 parcels then goes into the sum only once.
Let’s take three orders, two of them for $40.
SELECT SUM(amount)
FROM orders
JOIN (SELECT DISTINCT order_id
FROM parcels) AS shipped
ON shipped.order_id = orders.id| id | amount |
|---|---|
| 101 | $40 |
| 102 | $25 |
| 103 | $40 |
| id | order_id |
|---|---|
| 501 | 101 |
| 502 | 101 |
| 503 | 102 |
| 504 | 103 |
| order_id |
|---|
| 101 |
| 102 |
| 103 |
| SUM(amount) |
|---|
| $105 |
SELECT SUM(DISTINCT amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
result SUM(DISTINCT amount) $65
SELECT SUM(amount) FROM orders JOIN (SELECT DISTINCT order_id FROM parcels) AS shipped ON shipped.order_id = orders.idorders id amount 101 $35 102 $50 103 $20 104 $70 parcels id order_id 501 101 502 101 503 103 504 104 505 104 506 104 SELECT SUM(DISTINCT amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 $45 102 $15 103 $45 104 $80 parcels id order_id 501 103 502 102 503 102 504 101 505 103 506 104
How do you quickly spot a total doubled by a join?
It goes faster if you first count each order’s parcels: that is how many times its amount goes into the join with parcels.
SELECT SUM(amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 $25 102 $60 103 $15 104 $80 parcels id order_id 501 101 502 102 503 102 504 103 SELECT COUNT(DISTINCT orders.id) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 $20 102 $45 103 $35 104 $50 parcels id order_id 501 104 502 102 503 101 504 101 505 101 SELECT SUM(DISTINCT amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 $70 102 $25 103 $70 104 $15 parcels id order_id 501 103 502 102 503 101 504 101 SELECT SUM(amount) FROM orders JOIN (SELECT DISTINCT order_id FROM parcels) AS shipped ON shipped.order_id = orders.idorders id amount 101 $40 102 $80 103 $50 104 $35 parcels id order_id 501 101 502 102 503 102 504 102 505 104 506 104