HAVING: Filter Groups
The HAVING clause filters the groups made by GROUP BY.
It works like WHERE, but it is used after the rows are grouped.
Use HAVING when you want to keep or remove whole groups based on an aggregate value.
Syntax
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;
Example 1: Departments with More Than 4 Employees
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 4
ORDER BY department;
Output:
department | count
------------+-------
IT | 6
Sales | 6
(2 rows)
This returns only the departments that have more than 4 employees.
Example 2: Departments Where Everyone Is 26 or Older
SELECT department, MIN(age)
FROM employees
GROUP BY department
HAVING MIN(age) >= 26
ORDER BY department;
Output:
department | min
------------+-----
Finance | 27
Marketing | 29
(2 rows)
This returns the departments where the youngest employee is 26 or older.
Tip
- Use
WHEREto filter rows before grouping. - Use
HAVINGto filter groups after grouping.
The HAVING clause lets you choose which groups appear in your final result.
In the exercise, find the departments that have fewer than 3 employees.