Data · SQL

Why SUM doubles after a SQL join

Published

A $30 order shipped in 2 parcels: how many rows does the join of orders and parcels return?

From one order in two parcels to a correct total, in five steps:

  1. Spot the repeated amount
  2. Redo the wrong total by hand
  3. Count orders with COUNT(DISTINCT)
  4. Fix the total before the join
  5. Practice

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
orders
idamount
101$40
102$25
103$60
parcels
idorder_id
501101
502102
503102
504103
result
orders.idamount
101$40
102$25
102$25
103$60
  1. Which parcels carry the number of order 102? Tap them in the table.

    Look for 102 in the order_id column.

  2. The join writes one row per order-parcel pair (see why). Each row copies the amount of its order.

    rows

  1. SELECT orders.id, amount
    FROM orders
    JOIN parcels
      ON parcels.order_id = orders.id
    orders
    idamount
    101$35
    102$80
    103$15
    104$50
    parcels
    idorder_id
    501101
    502102
    503102
    504102
    505103
    506104

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
orders
idamount
101$40
102$25
103$60
parcels
idorder_id
501101
502102
503102
504103
join
orders.idamount
101$40
102$25
102$25
103$60
result
SUM(amount)
$150
  1. Add up the amounts in the orders table, each one once.

    $

  2. SUM adds up the rows of the join. An order’s amount goes in once for each of its parcels.

    $

  1. SELECT SUM(amount)
    FROM orders
    JOIN parcels
      ON parcels.order_id = orders.id
    orders
    idamount
    101$50
    102$15
    103$70
    parcels
    idorder_id
    501101
    502102
    503102
    504102
    505103
    $

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
orders
idamount
101$40
102$25
103$60
parcels
idorder_id
501101
502101
503102
504103
505103
506103
join
orders.idamount
101$40
101$40
102$25
103$60
103$60
103$60
result
COUNT(*)COUNT(DISTINCT orders.id)
63
  1. COUNT(*) counts the rows of the join: one for each parcel that finds its order.

  2. DISTINCT keeps each order number once, however many rows it has.

  1. SELECT COUNT(*)
    FROM orders
    JOIN parcels
      ON parcels.order_id = orders.id
    orders
    idamount
    101$15
    102$50
    103$35
    104$70
    parcels
    idorder_id
    501101
    502102
    503102
    504102
    505103
    506104
    507104

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
orders
idamount
101$40
102$25
103$40
parcels
idorder_id
501101
502101
503102
504103
shipped
order_id
101
102
103
result
SUM(amount)
$105
  1. SELECT DISTINCT keeps each order_id of the parcels table once.

    rows

  2. Each order finds a single row in shipped: its amount goes into the sum once.

    $

  3. SELECT SUM(DISTINCT amount)
    FROM orders
    JOIN parcels
      ON parcels.order_id = orders.id
    result
    SUM(DISTINCT amount)
    $65

    DISTINCT keeps each different amount once, even if it comes from two orders.

    $

  1. SELECT SUM(amount)
    FROM orders
    JOIN (SELECT DISTINCT order_id
          FROM parcels) AS shipped
      ON shipped.order_id = orders.id
    orders
    idamount
    101$35
    102$50
    103$20
    104$70
    parcels
    idorder_id
    501101
    502101
    503103
    504104
    505104
    506104
    $

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.

  1. SELECT SUM(amount)
    FROM orders
    JOIN parcels
      ON parcels.order_id = orders.id
    orders
    idamount
    101$25
    102$60
    103$15
    104$80
    parcels
    idorder_id
    501101
    502102
    503102
    504103
    $