Investigate: why does the report show too many orders?

The report

The sales team’s dashboard shows, for each customer, the number of orders (excluding cancelled ones) and the revenue from them. The query:

SELECT c.name, count(*) AS orders, sum(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY c.name
ORDER BY c.name;

    name    | orders | revenue
------------+--------+----------
 Asha Rao   |      6 | 23197.00
 Ben Okafor |      2 | 41997.00
 Chen Wei   |      1 |  3600.00
 Divya Iyer |      4 | 26695.00
 Farhan Ali |      3 | 24998.00
 Grace Kim  |      2 | 23499.00

Asha says she placed three orders, not six.

1. Establish the truth

Count orders without touching order_items:

SELECT c.name, count(*) AS orders
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE o.status <> 'cancelled'
GROUP BY c.name ORDER BY c.name;

Customer	Report says	Actually	Yours
Asha Rao	6	3
Divya Iyer	4	2
Farhan Ali	3	2
Grace Kim	2	1
Ben Okafor, Chen Wei	correct

2. Explain it

The report joins each order to its items before grouping. An order with two items becomes two rows, so count(*) counts items, not orders. Asha’s three orders contain six items. Ben’s and Chen’s numbers are right only because each of their orders has exactly one item.

This is the join fan-out from Joins, now hidden inside an aggregate. The revenue is correct because each item row carries its own amount, so adding up item rows is exactly right. Anything computed from the order count is wrong: an “average order value” of 23,197 ÷ 6 ≈ 3,866 instead of 23,197 ÷ 3 ≈ 7,732, less than half the truth.

3. Fix it

Count what you mean: distinct orders.

SELECT c.name, count(DISTINCT o.id) AS orders, sum(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY c.id, c.name
ORDER BY c.name;

    name    | orders | revenue
------------+--------+----------
 Asha Rao   |      3 | 23197.00
 Ben Okafor |      2 | 41997.00
 Chen Wei   |      1 |  3600.00
 Divya Iyer |      2 | 26695.00
 Farhan Ali |      2 | 24998.00
 Grace Kim  |      1 | 23499.00

Two changes, each fixing a real bug:

• count(DISTINCT o.id) counts orders, however many items each has.
• GROUP BY c.id, c.name instead of GROUP BY c.name. Two different customers who share a name would otherwise be merged into one row. Group by the key; carry the name along for display.

4. Check the total

Reports should reconcile with a simple query. Total revenue from non-cancelled orders:

SELECT sum(oi.quantity * oi.unit_price)
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled';

	Ours	Yours
Sum of the report’s revenue column	143,986.00
Direct total	143,986.00

They match, so the revenue column is sound and only the counts were wrong.

Check yourself

1. Why did count(*) report six orders for Asha?

   Show answer

   The join to order_items produced one row per item, and Asha’s three orders contain six items. count(*) counted the rows, which were items.

   I could explain this without looking

2. Why was the revenue column correct when the order count was not?

   Show answer

   Revenue is item-level: each joined row carries its own item amount, so summing the item rows gives the right total. The count was meant to be order-level, but each order appeared once per item.

   I could explain this without looking

3. Why group by c.id, c.name rather than c.name?

   Show answer

   Names are not unique. Two customers with the same name would be merged into one group. Grouping by the primary key keeps them apart.

   I could explain this without looking