What is a "grouping trap" with COUNT?
SQLLevel: IntermediateAggregations & GROUP BY
What is a "grouping trap" with COUNT?
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
When joining and then grouping, you can accidentally multiply counts.
-- Suppose orders has 3 rows for customer 1, and order_items has 5 rows total for those orders
-- order_count is inflated because the join expands rows before GROUP BY
SELECT c.customer_id,
COUNT(o.order_id) AS order_count,
COUNT(oi.item_id) AS item_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id;
-- Fix: use COUNT(DISTINCT ...)
SELECT c.customer_id,
COUNT(DISTINCT o.order_id) AS order_count,
COUNT(oi.item_id) AS item_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id;
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT