Aggregating with LEFT JOINs
You'll learn how to safely combine LEFT JOINs with aggregate functions while avoiding duplicate-row traps and NULL confusion.
The Core Challenge
When you use a LEFT JOIN with GROUP BY and aggregate functions like COUNT() or SUM(), you need to be careful about two common pitfalls:
- Duplicate rows from the join can inflate your aggregates
- NULL values from unmatched rows can affect your counts differently than expected
How It Works
Imagine you're counting orders per customer. If you LEFT JOIN customers to orders and then COUNT(*), customers with multiple orders get counted multiple times before grouping happens. The duplication inflates your totals.
The safe pattern:
SELECT c.customer_id,
c.name,
COUNT(o.order_id) AS order_count,
SUM(o.amount) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
Notice we use COUNT(o.order_id) (a specific column from the right table), not COUNT(*). This way, customers with no orders return 0 instead of 1, because NULLs aren't counted.
Watch Out For
- COUNT(*) counts all rows, including those with NULLs → wrong totals
- COUNT(right_table_column) ignores NULLs → correct for "how many matches"
- SUM() naturally ignores NULLs, so unmatched rows contribute nothing (which is usually what you want)
Think of it like taking attendance: COUNT(*) counts empty chairs, while COUNT(student_name) only counts actual students present.
Key Takeaway: When aggregating with LEFT JOINs, always reference specific columns from the right table in your aggregate functions (like COUNT(o.order_id)) to handle unmatched rows correctly, and group by the left table's identifying columns to avoid inflated counts.