Course contentsShow
← Data Science
Lesson 895 of 2,14523. SQL Aggregation and GroupingFree lesson

Combining WHERE and GROUP BY

Filtering rows before grouping with WHERE to control which records enter the aggregation process.

Combining WHERE and GROUP BY

What You'll Learn

You'll learn how to filter rows before grouping them, so that only the records you want participate in your aggregate calculations.

The Core Idea

Think of WHERE as a bouncer at a club and GROUP BY as the organizer inside who arranges people into groups. The bouncer (WHERE) decides who gets in first, then the organizer (GROUP BY) works only with the people who made it through the door.

In SQL, WHERE filters individual rows before any grouping happens. This means you can exclude unwanted data early, and only the qualifying rows will be grouped and aggregated.

How It Works

The order matters:

  1. WHERE examines each row and decides: keep it or drop it
  2. Only the kept rows proceed to GROUP BY
  3. GROUP BY organizes those filtered rows into groups
  4. Aggregate functions calculate results for each group

Example scenario: You want to find average order values by region, but only for orders placed in 2023.

SELECT region, AVG(order_amount) AS avg_order
FROM orders
WHERE order_year = 2023
GROUP BY region

Here, WHERE order_year = 2023 filters out all non-2023 orders first. Then GROUP BY region organizes the remaining 2023 orders by region, and AVG() calculates the average for each region.

Why This Matters

Without WHERE, you'd be grouping all orders from all years, which would give you incorrect averages. Filtering first ensures your aggregates reflect exactly the subset of data you care about.

Key Takeaway: Use WHERE before GROUP BY to filter which rows enter the grouping and aggregation process—it acts as a pre-filter that shapes your final grouped results.