What is HAVING?

The HAVING clause is used to filter grouped results.

It is commonly used after GROUP BY, especially when you want to filter groups using aggregate values such as count, total, average, minimum value, or maximum value.

How HAVING works

HAVING is written after GROUP BY because it filters the grouped result, not the original individual rows.

SELECT
    column_name,
    aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_name) condition;

Example

SELECT
    department,
    COUNT(*) AS total_employees
FROM Employees
GROUP BY department
HAVING COUNT(*) > 1;

Example table

We will group this Employees table by department, then use HAVING to filter the grouped result.

Filtering groups with HAVING

This query groups employees by department, counts the rows in each department, and then keeps only the departments with more than one employee.

SELECT
    department,
    COUNT(*) AS total_employees
FROM Employees
GROUP BY department
HAVING COUNT(*) > 1;

Result

WHERE vs HAVING

WHERE and HAVING both filter data, but they are used at different stages of the query.

HAVING with an average value

SELECT
    department,
    AVG(salary) AS average_salary
FROM Employees
GROUP BY department
HAVING AVG(salary) > 45000;

Key takeaway