Can I filter the groups?
Harry’s department breakdown was a hit. Now finance wants a narrower version: “Which departments actually have more than two people in them?”
I already have the department, COUNT(*) query. I'll just tack on a condition — WHERE COUNT(*) > 2.
Can I filter the groups, not just the rows? Let's see what SQL thinks of that.
Try it: Try filtering on the aggregate with WHERE.
Run your query to see results here.
Error. WHERE runs before any grouping or aggregating happens — it’s deciding which individual employee rows are even allowed into the query, one row at a time. At that point, COUNT(*) hasn’t been calculated yet for anyone. Asking WHERE COUNT(*) > 2 is like asking “is the total more than two” before there is a total.
SQL runs these clauses in a specific order: FROM, then WHERE (filters individual rows), then GROUP BY (bundles what survived), then the aggregates get calculated, and only after that can you filter on the aggregate result. That last filter has its own keyword: HAVING.
WHERE decides which rows are allowed to stay. HAVING decides which groups survive.
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 2
ORDER BY department;| department | headcount |
|---|---|
| Engineering | 5 |
| Product | 3 |
| Sales | 3 |
| Support | 3 |
Same grouping as before, but now Marketing — only two people — is gone from the result. Nothing about how the rows got grouped changed; HAVING just throws away the finished groups that don’t meet the bar.
Finance: “One more thing — only count people hired before 2021.” Now you need both: WHERE to drop the newer hires before grouping, and HAVING to filter the resulting department groups after:
SELECT department, COUNT(*) AS early_hires
FROM employees
WHERE hire_date < '2021-01-01'
GROUP BY department
HAVING COUNT(*) > 1
ORDER BY department;| department | early_hires |
|---|---|
| Engineering | 3 |
| Product | 2 |
Sales, Support, and Marketing each had exactly one employee hired before 2021 — they get filtered out by WHERE down to one row apiece, then HAVING COUNT(*) > 1 drops those single-person groups entirely. Two different filters, two different jobs, both in one query.
Running…
Try it: What if you swap HAVING COUNT(*) > 1 back to WHERE COUNT(*) > 1 in that same query?
Run your query to see results here.
Still errors, for the same reason as the very first attempt. Bundling a real row-level condition (hire_date < ’2021-01-01’) with an aggregate condition (COUNT(*) > 1) inside WHERE doesn’t make the aggregate exist any sooner. WHERE only ever gets to ask questions a single row can answer by itself.
Ops wants to know which customers are repeat buyers: customers with more than 2 completed orders. Return each qualifying customer’s customer_id and their completed order count, sorted by customer_id.
Hint 1
Two filters are hiding in this question — one about which orders even count, and one about the final count per customer. Which one is a WHERE, and which one is a HAVING?
Hint 2
Filter to completed orders first with WHERE, then GROUP BY customer_id to get one row per customer before you can compare counts.
Hint 3
SELECT customer_id, COUNT(*) AS completed_orders FROM orders WHERE status = 'completed' GROUP BY customer_id HAVING COUNT(*) > 2 ORDER BY customer_id;
Solution
Running…
The question “repeat buyers” actually hides two separate filters: which rows count as real orders in the first place (completed, not cancelled or refunded), and which customers end up with enough of them. WHERE answers the first question before grouping even starts. HAVING answers the second, after the counting is done. Mixing them up is the single most common GROUP BY mistake — and now you know exactly why SQL won’t let you.
WHERE filters individual rows before grouping. HAVING filters groups after aggregation. If your condition involves COUNT, SUM, AVG, MIN, or MAX, it belongs in HAVING — not WHERE.