WHERE vs HAVING in SQL: filter rows, then groups
The question: Which departments have at least 2 employees earning more than 60,000? Show the department and how many.
employees
| emp_id | dept | salary |
|---|---|---|
| 1 | Sales | 80000 |
| 2 | Sales | 65000 |
| 3 | Sales | 40000 |
| 4 | Tech | 90000 |
| 5 | Tech | 50000 |
| 6 | Tech | 45000 |
| 7 | HR | 70000 |
| 8 | HR | 75000 |
This query looks right. Run it, decide if it is, and fix it.
Show the answer and why the first query is wrong
Why the usual answer is wrong
SELECT dept, COUNT(*) AS earners FROM employees GROUP BY dept HAVING COUNT(*) >= 2 AND salary > 60000;
The salary filter should remove rows before counting. In HAVING it runs after grouping, so COUNT(*) counts everyone in the department and salary is just one row's value.
The answer
SELECT dept, COUNT(*) AS earners FROM employees WHERE salary > 60000 GROUP BY dept HAVING COUNT(*) >= 2;
WHERE filters rows before they're grouped; HAVING filters the groups after. Keep only the people earning over 60,000, then keep the departments with 2 or more of them.
A rule that rarely fails: a condition on a column goes in WHERE, a condition on COUNT, SUM or AVG goes in HAVING. PostgreSQL rejects the first query outright.
Practise more like this, on real business data
324 interview-style questions on 35 tables with the duplicates, NULLs and missing weeks interviewers test. Free.