HAVING

Objectives

By the end of this lesson, you should be able to:

• Filter groups (not individual rows) with HAVING
• Explain the difference between WHERE and HAVING
• Combine WHERE and HAVING in the same query

💡 Why this matters: WHERE filters rows before grouping happens. But “only show departments with more than 2 employees” is a condition on the group itself, a count that doesn’t exist until after GROUP BY runs. WHERE can’t express that, HAVING can.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the same employees table as the previous lesson.

HAVING Filters Groups

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

 department  | employee_count
--------------+-----------------
 Engineering |               3

Only Engineering has more than 2 employees, so it’s the only group left in the result. HAVING COUNT(*) > 2 couldn’t be written as WHERE COUNT(*) > 2, COUNT(*) doesn’t exist yet at the point WHERE runs, rows haven’t been grouped into anything countable.

HAVING with Other Aggregates

SELECT department, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000
ORDER BY department;

 department  | avg_salary
--------------+------------
 Engineering |   86000.00

Just like GROUP BY, HAVING works with any aggregate function, not just COUNT(). The condition is checked against the group’s computed value, not any individual row’s salary.

WHERE and HAVING Together

SELECT department, COUNT(*) AS cnt
FROM employees
WHERE hire_date > '2019-01-01'
GROUP BY department
HAVING COUNT(*) >= 2
ORDER BY department;

 department  | cnt
--------------+-----
 Engineering |   2
 Marketing   |   2
 Sales       |   2

Both can appear in the same query, and PostgreSQL applies them in a specific order: WHERE filters individual rows first (here, excluding anyone hired on or before 2019-01-01, which removes Priya and Taylor from Engineering, and Devon has no matching issue since NULL department doesn’t affect hire_date filtering), then GROUP BY forms groups from what’s left, then HAVING filters those groups. This is why Priya and Taylor (early hires) don’t count toward Engineering’s total of 2 here, even though Engineering had 3 employees total before the WHERE clause ran.

Try It

1. Write a query showing every department with an average salary below 70000, using HAVING.
2. Write a query showing every department with more than 1 employee, using HAVING COUNT(*) > 1.
3. Write a query that first filters to employees earning more than 60000 with WHERE, then groups by department, then keeps only groups with 2 or more employees using HAVING.
4. Explain, in your own words, why WHERE COUNT(*) > 2 raises an error but HAVING COUNT(*) > 2 doesn’t.

Recap

• HAVING filters groups after GROUP BY forms them, based on an aggregate condition.
• WHERE filters individual rows before grouping happens, WHERE can’t reference an aggregate function.
• When both appear together, PostgreSQL applies WHERE first, then GROUP BY, then HAVING, in that order.

Next lesson: grouping by more than one column at once.