Course contentsShow
← Data Science
Lesson 933 of 2,14524. SQL JoinsFree lesson

Aggregating with LEFT JOINs

Combining LEFT JOINs with GROUP BY and aggregate functions while avoiding pitfalls of duplicate rows and NULLs.

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:

  1. Duplicate rows from the join can inflate your aggregates
  2. 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.