HAVING — filter on aggregates

WHERE filters individual rows before grouping. HAVING filters entire groups after aggregation.

Syntax

SELECT grouping_column, aggregate_function(col) AS alias
FROM table_name
GROUP BY grouping_column
HAVING aggregate_condition;

Example: departments with 5 or more employees

SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 5
ORDER BY headcount DESC;

HAVING vs WHERE

-- WHERE filters rows before grouping
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2000-01-01'   -- only recent hires
GROUP BY department_id
HAVING AVG(salary) > 8000;       -- only departments with high avg salary

Execution order (full picture)

1. FROM — identify source table
2. WHERE — filter individual rows
3. GROUP BY — form groups
4. HAVING — filter groups
5. SELECT — compute output columns
6. ORDER BY — sort
7. LIMIT / OFFSET — paginate

Can I use an alias in HAVING?

In most databases (including PostgreSQL), you cannot reference a SELECT alias in HAVING because SELECT runs after HAVING. Repeat the aggregate expression:

-- CORRECT
HAVING COUNT(*) >= 5

-- ERROR in most databases
HAVING headcount >= 5

Tips

• HAVING without GROUP BY operates on the whole table as one group.
• Any aggregate function (SUM, AVG, MIN, MAX) can appear in HAVING.