Common mistake

COUNT or SUM Too High After a SQL JOIN

A one-to-many join repeats each row, so totals get multiplied. Count the thing you actually mean.

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 join orders to order_items, an order with 4 line items becomes 4 rows. Now count(*) counts line items, not orders, and sum(order_total) adds the same total once per line item.

The fix

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

Wrong: counts line items

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

Right: counts orders

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, so count(*) 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.

Tell-tale sign

If a number jumps the moment you add a join, suspect fan-out. Check it by comparing row counts before and after the join. EXPLAIN ANALYZE shows both counts in one plan; see EXPLAIN and query plans.