Filtering Groups: HAVING vs. WHERE
The Follow-Up Question
The regional breakdown was a hit. But then the VP of Sales followed up: *"That list has 50 countries. Can you show me only the ones with revenue over $500,000?"*
My gut told me to use `WHERE`. But wait... Where do I put the filter? I don't have `total_revenue` as a "Real" column; it's calculated.
The Quest: The "When" of Filtering
In SQL, the order of operations matters:
1. `WHERE` filters **before** grouping (on individual rows).
2. `HAVING` filters **after** grouping (on the aggregated results).
The Implementation: The Right Tool for the Job
Filtering Rows BEFORE
If I only want to look at orders from 2025:
SELECT
country,
SUM(order_amount) AS total_revenue
FROM orders
WHERE order_year = 2025 -- This filters ROWS first
GROUP BY country;
Filtering Groups AFTER
If I want to see only countries whose *result* is over 500K:
SELECT
country,
SUM(order_amount) AS total_revenue
FROM orders
GROUP BY country
HAVING SUM(order_amount) > 500000; -- This filters the AGGREGATED result
Both Together
-- 2025 orders only, and only countries over 500K
SELECT country, SUM(order_amount) as total_revenue
FROM orders
WHERE order_year = 2025
GROUP BY country
HAVING SUM(order_amount) > 500000;
The "Oops" Moment
I tried to use an Alias in HAVING: `HAVING total_revenue > 500000`. In many databases (like PostgreSQL), this works! But in MySQL, it didn't. SQL can be fickle about when you can reference an alias.
**Pro Tip**: To be safe, repeat the full aggregation expression (`SUM(order_amount)`) inside the `HAVING` clause.
The Victory
The VP got a focused list of just 8 high-revenue countries. No scrolling, no filtering in Excel. The report was "Pre-Curated." This is the secret to becoming indispensable: give stakeholders exactly what they need, nothing more.
Your Task for Today
Create a `GROUP BY` query and then use `HAVING` to filter out groups that have less than 10 items using `HAVING COUNT(*) >= 10`.
*Day 20: Time is Money—Grouping by Date.*