HAVING clause

The HAVING clause can be used after the GROUP BY clause to specify filter criteria after grouping is complete. In syntax, the HAVING clause is identical to the WHERE clause, but while the WHERE clause is used before grouping, the HAVING clause is used after it.

Syntax

SELECT ...
FROM ...
GROUP BY ...
HAVING <predicate>
[ ... ]

Parameters

  • <predicate>
    Boolean expression.

Examples

  • Let’s count the number of records in the employees table that contain each of the different departments, discarding departments with less than 10:

    SELECT department, count(*)
FROM employees
GROUP BY department
HAVING count(*) >= 10;

  • Calculate the average salary for each department of each division , but only for those departments where the average salary is greater than the median salary:

    SELECT division, department, avg(salary)
FROM employees
GROUP BY division, department
HAVING avg(salary) > median(salary);