COUNT or SUM Too High After a SQL JOIN

The symptom

Your query runs fine, but a count(*) or sum() comes back far larger than it should be after you add a join. A customer with a handful of orders suddenly shows several times that many; revenue looks doubled.

Why it happens

Joining a one-to-many table fans out the rows. When you joinorders to order_items, an order with 4 line items becomes 4 rows. Now count(*) counts line items, not orders, andsum(order_total) adds the same total once per line item.

The fix

Count or sum the thing you actually mean, often withdistinct, or by aggregating the child table on its own before joining.

select c.customer_id, count(*) as orders
from customers as c
join orders      as o  on o.customer_id = c.customer_id
join order_items as oi on oi.order_id   = o.order_id
group by c.customer_id
order by c.customer_id

select c.customer_id, count(distinct o.order_id) as orders
from customers as c
join orders      as o  on o.customer_id = c.customer_id
join order_items as oi on oi.order_id   = o.order_id
group by c.customer_id
order by c.customer_id

Customer 1 has three Garden Shop orders and six line items across them, socount(*) reports 6 where count(distinct o.order_id)reports 3. Only one row in that result is the same on both sides: customer 9, whose single order has a single line item. That is what makes fan-out dangerous — it hides completely on the small, simple rows you are most likely to spot-check.

Learn more

• Lesson 9.3 · Wrong answers without errors
• Cheatsheet · Joins
• Reference · JOIN types