Filtering groups with HAVING
WHERE filters rows before grouping. HAVING filters the groups afterwards.
In this lesson 3 sections
You want reps with at least five won deals. This fails:
SELECT owner_id, COUNT(*) AS won_deals
FROM opportunities
WHERE is_won
AND COUNT(*) >= 5 -- error: no groups exist yet
GROUP BY owner_id;WHERE runs before GROUP BY, so there are no counts to check yet. HAVING runs after grouping:
SELECT owner_id, COUNT(*) AS won_deals
FROM opportunities
WHERE is_won -- 1. keep only won deals
GROUP BY owner_id -- 2. one pile per rep
HAVING COUNT(*) >= 5; -- 3. keep reps with 5 or moreWhich one do I use?
| Ask yourself | Use |
|---|---|
Does the condition look at one row? (is_won, amount > 1000) |
WHERE |
Does it look at a group’s total? (COUNT(*) >= 5, SUM(amount) > 100000) |
HAVING |
Put row filters in WHERE even when the query also has HAVING. Throwing rows away early is faster, and the intent is clearer.
Using the alias in HAVING
Most databases make you repeat the aggregate in HAVING:
HAVING COUNT(*) >= 5 -- works everywhere
HAVING won_deals >= 5 -- MySQL and SQLite onlyHAVING runs before SELECT (lesson 3), so the alias doesn’t technically exist yet. Repeat the aggregate to stay portable.
Snowflake notes
Snowflake also lets you use the SELECT alias in HAVING, so HAVING won_deals >= 5 works there. Repeat the aggregate anyway if the query might move to SQL Server or PostgreSQL.
Try it
Show each lead source whose open pipeline (the total amount of open deals) is over $250,000, largest first. Return lead_source and the total.
Write it in the sandbox below, then press Check answer.
SQL sandbox
Run queries against the sample CRM tables (accounts, opportunities,users, contacts and opportunity_history), right in your browser. Nothing you run leaves your computer.