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:
WHEREexamines each row and decides: keep it or drop it- Only the kept rows proceed to
GROUP BY GROUP BYorganizes those filtered rows into groups- 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.