Join fan-out & COUNT(DISTINCT)

Avoid double counting

Lesson 4 of 20 · 11 min · Module 1: Joins beyond the basics

Joining a one-to-many relationship multiplies rows. An order with three items becomes three rows, and counting orders after that join counts it three times. This silent bug appears in real reports all the time.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Recognise row multiplication in joins
Count distinct parents after a join
Choose when to aggregate before joining

The idea

After joining orders to order_items, each order appears once per item. COUNT(DISTINCT o.id) counts each order once, while SUM(oi.quantity) correctly adds the item rows. When you need sums from two different detail tables, aggregate each one separately first.

SELECT COUNT(*) AS joined_rows, COUNT(DISTINCT o.id) AS orders
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.customer_id = 1;

01Rows multiply

Ana has three orders with 2, 1 and 1 item lines. After the join she has four rows, so COUNT(*) reports four orders instead of three.

02Distinct parents, summed children

COUNT(DISTINCT o.id) collapses the repeated order ids. SUM(oi.quantity) should add every item row, so it stays without DISTINCT.

03Pre-aggregate when two lists are involved

Joining both order_items and, say, payments to orders multiplies twice. Summarise each one per order in a CTE first, then join the summaries, which have one row per order.

WITH per_order AS (
  SELECT order_id, SUM(quantity) AS units
  FROM order_items GROUP BY order_id
)
SELECT COUNT(*) FROM per_order;

Output

Your turn

For every customer with at least one order that is not cancelled, return name, the number of those orders as orders, and the total quantity of items in them as units. Sort by units, highest first, then by name.

What the checks look for

Counts each order once and every unit exactly

Try it in the editor →

Take this with you

After a one-to-many join, count parents with DISTINCT and sum children normally.

Go a little further

Add the total spent per customer in the same query and check that it matches a separate query on order_items.