HAVING

WHERE filters individual rows before they are grouped. But what if you want to filter based on the result of an aggregate — for example, only showing departments whose total sales exceed a threshold? That’s exactly what HAVING is for.

WHERE filters rows, HAVING filters groups

	WHERE	HAVING
Runs	Before grouping	After grouping
Filters	Individual rows	Entire groups
Can reference aggregates?	No	Yes
Can reference raw columns?	Yes	Yes (if grouped or aggregated)

The error WHERE gives you

WHERE cannot see aggregate results

Aggregates like COUNT(*) don’t exist yet at the point WHERE runs — grouping and aggregation haven’t happened. Trying to filter on one there raises an error:

Error — aggregate function not allowed in WHERE

SQL

-- This fails: "aggregate functions are not allowed in WHERE"
SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE COUNT(*) > 5
GROUP BY department;

The HAVING fix

HAVING runs after GROUP BY has produced its groups and after aggregate functions have been computed, so it can filter on them directly:

Correct — filter groups with HAVING

SQL

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

 department  | employee_count
-------------+----------------
 Engineering |             12
 Sales       |              8

Departments with five or fewer employees are excluded entirely from the result, because their group didn’t satisfy the HAVING condition — but the counting itself still happened across all departments first.

Combining WHERE and HAVING

The two work together naturally: WHERE trims rows before grouping (for efficiency and to exclude rows that shouldn’t count at all), and HAVING trims groups after aggregation:

Active employees only, then filter by department size

SQL

SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
WHERE is_active = TRUE
GROUP BY department
HAVING COUNT(*) > 5 AND AVG(salary) > 70000;

Here, inactive employees are excluded before any grouping happens (WHERE), and then only departments with more than five active employees and an average salary above 70,000 make it into the final result (HAVING).

HAVING without GROUP BY

HAVING can technically be used without GROUP BY — in that case the entire result set is treated as a single group. This is rarely used in practice since a WHERE clause combined with an aggregate in SELECT usually reads more clearly for that case.

WHERE filters individual rows before grouping and cannot reference aggregate functions
HAVING filters entire groups after grouping and aggregation, and can reference aggregate functions freely
Use WHERE to cut down rows early for efficiency; use HAVING to filter based on a group's computed values