Joins with GROUP BY, and the double-counting trap

Part of the free LogicWiz SQL & Databases course, Module 5: Joins. The queries and practice run in the app on real Postgres.

Aarav paid ₹30 in delivery fees in July. The new dashboard says ₹60. Nobody changed the data: one extra JOIN to order_items quietly doubled it. Let's see how.

You'll learn to

Count and sum through a join, including zero rows
Choose COUNT(*) or COUNT(column) after a LEFT JOIN
Spot fan-out: when a join repeats rows and inflates SUM
Fix it by aggregating at the right level

Learn

Join first, then group

A query runs FROM (with its joins) first, then WHERE, then GROUP BY. So you can group the joined rows: join orders to restaurants, then GROUP BY the restaurant, and every aggregate works on that restaurant's orders.

To include restaurants with zero orders, start from restaurants and LEFT JOIN, exactly like the anti-join, but count instead of filtering.

Orders per restaurant, zeros included

SELECT r.name, COUNT(o.order_id) AS orders
FROM restaurants r
LEFT JOIN orders o ON o.restaurant_id = r.restaurant_id
GROUP BY r.restaurant_id, r.name
ORDER BY orders DESC, r.name;

Pizza Corner shows 0. We group by restaurant_id too, so two restaurants with the same name would stay separate.

After a LEFT JOIN, COUNT(*) counts rows, and Pizza Corner still has one row (with NULL order columns). So COUNT(*) says 1. COUNT(o.order_id) counts non-NULL values and says 0. When counting the right-hand table, always count one of its columns, ideally its primary key.

Fan-out: one row becomes many

Joining a "one" table to a "many" table repeats the "one" row once per match. Order 1001 has 2 lines in order_items, so after the join it appears twice, and so does its ₹30 delivery fee.

That's fine for counting dishes. But SUM(o.delivery_fee) now adds ₹30 twice. This is fan-out, and it is the most common reason a report is "a bit too high". Each row of the result should be one thing; ask yourself: one row per what?

Watch the fee double

-- wrong: one row per order LINE, so each fee repeats
SELECT SUM(o.delivery_fee) AS fees
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.customer_id = 1;

-- right: one row per ORDER
SELECT SUM(o.delivery_fee) AS fees
FROM orders o
WHERE o.customer_id = 1;

Aarav has 4 orders but 8 order lines. Order 1001 (₹30 fee) has 2 lines, so the fee is counted twice: ₹60 instead of ₹30.

Measure at the level it lives

SELECT r.name,
       COUNT(DISTINCT o.order_id) AS orders,
       SUM(oi.qty) AS dishes,
       SUM(oi.qty * m.price) AS food_revenue
FROM restaurants r
JOIN orders o ON o.restaurant_id = r.restaurant_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN menu_items m ON m.item_id = oi.item_id
WHERE o.status = 'delivered'
GROUP BY r.restaurant_id, r.name
ORDER BY food_revenue DESC, r.name;

qty and price live on the order line, so summing them over lines is right. Orders are repeated, so count them with COUNT(DISTINCT o.order_id). Fees live on the order: don't sum them here.

Fix by aggregating first

SELECT c.name, f.fees
FROM customers c
JOIN (SELECT customer_id, SUM(delivery_fee) AS fees
      FROM orders
      GROUP BY customer_id) f ON f.customer_id = c.customer_id
ORDER BY f.fees DESC, c.name;

The query in brackets sums fees per customer before anything is joined, so nothing can repeat. A query inside FROM is a derived table; Module 6 covers it properly.

"My SUM is too high after adding a join. Why?" is a favourite interview follow-up. Answer: the join fans out: a one-to-many join repeats the parent row, so its columns are summed once per child. Fixes: drop the join you don't need, use COUNT(DISTINCT …) for counts, or aggregate each table to the same grain before joining.

In one line

Count the right table with COUNT(right.pk); a one-to-many join repeats parent rows, so sum parent columns before joining.