HAVING
Learning Focus
Use this lesson to understand HAVING — filtering group results that WHERE cannot touch.
HAVING vs WHERE
| Clause | Filters | Runs |
|---|---|---|
WHERE | Individual rows | Before GROUP BY |
HAVING | Aggregated groups | After GROUP BY |
Basic Syntax
SELECT column, aggregate_function(col)
FROM table
GROUP BY column
HAVING aggregate_function(col) condition;
Examples
Example 1: Departments with More Than 1 Employee
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;
Example 2: Departments with Average Salary Above Threshold
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 75000
ORDER BY avg_salary DESC;
Example 3: Combine WHERE and HAVING
-- Only active employees, departments with > 1 person
SELECT department, COUNT(*) AS count
FROM employees
WHERE active = true
GROUP BY department
HAVING COUNT(*) > 1;
Example 4: HAVING with Multiple Conditions
SELECT department, MIN(salary) AS min_sal, MAX(salary) AS max_sal
FROM employees
GROUP BY department
HAVING MIN(salary) > 60000 AND MAX(salary) < 100000;
Common Pitfalls
| Pitfall | Consequence | Prevention |
|---|---|---|
| Using WHERE for aggregate conditions | Error or logic bug | Use HAVING for any condition on an aggregate |
| HAVING without GROUP BY | Returns entire table as one group | Always pair HAVING with GROUP BY |
| Referencing SELECT alias in HAVING | May not work in all DBs | DuckDB supports alias reference in HAVING |
Quick Reference
GROUP BY col HAVING COUNT(*) > 5
GROUP BY col HAVING AVG(salary) > 80000
GROUP BY col HAVING SUM(amount) BETWEEN 1000 AND 5000
What's Next
- Next: GROUPING SETS, ROLLUP, CUBE - Advanced multi-level aggregations.
- Module Overview - Return to this module index.