SQL HAVING

Summary: In this tutorial, you’ll learn how to use the SQL HAVING clause to filter groups based on a condition.

Introduction to SQL HAVING clause #

The GROUP BY clause groups rows of a result set into groups. To specify a condition for filtering groups, you use a HAVING clause.

If you use a HAVING clause without a GROUP BY clause, the HAVING clause behaves like a WHERE clause.

Here’s the syntax of the HAVING clause:

SELECT
  column1,
  column2,
  aggregate_function (column3)
FROM
  table1
GROUP BY
  column1,
  column2
HAVING
  group_condition;Code language: SQL (Structured Query Language) (sql)

Note that the HAVING clause appears immediately after the GROUP BY clause.

SQL HAVING clause examples #

We’ll use the following employees and departments tables from the sample database for the demonstration.

To get the managers and their direct reports, you use the GROUP BY clause to group employees by the managers and use the COUNT function to count the direct reports:

SELECT
  manager_id,
  COUNT(employee_id) direct_reports
FROM
  employees
WHERE
  manager_id IS NOT NULL
GROUP BY
  manager_id
ORDER BY
  direct_reports;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 manager_id | direct_reports
------------+----------------
        201 |              1
        102 |              1
        120 |              1
        205 |              1
        123 |              2
        103 |              4
        101 |              5
        114 |              5
        108 |              5
        100 |             14

To find the managers who have at least five direct reports, you add a HAVING clause to the query above as the following:

SELECT
  manager_id,
  COUNT(employee_id) direct_reports
FROM
  employees
WHERE
  manager_id IS NOT NULL
GROUP BY
  manager_id
HAVING
  COUNT(employee_id) >= 5
ORDER BY
  direct_reports;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 manager_id | direct_reports
------------+----------------
        101 |              5
        114 |              5
        108 |              5
        100 |             14

SQL HAVING with SUM function example #

The following statement calculates the sum of salary that the company pays for each department and selects only the departments with the sum of salary between 20000 and 30000.

SELECT
  department_id,
  SUM(salary) total_salary
FROM
  employees
GROUP BY
  department_id
HAVING
  SUM(salary) BETWEEN 20000 AND 30000
ORDER BY
  total_salary;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 department_id | total_salary
---------------+--------------
            11 |     20300.00
             3 |     24900.00
             6 |     28800.00

SQL HAVING with MIN function example #

To find the department that has employees with the lowest salary greater than 10000, you use the following query:

SELECT
  department_name,
  MIN(salary) min_salary
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
  department_name
HAVING
  MIN(salary) >= 10000
ORDER BY
  MIN(salary);Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 department_name  | min_salary
------------------+------------
 Public Relations |   10000.00
 Executive        |   17000.00Code language: PHP (php)

How the query works.

1. First, use the GROUP BY clause to group employees by department.
2. Second, use the MIN function to find the lowest salary per group.
3. Third, apply the condition to the HAVING clause.

SQL HAVING clause with AVG function example #

To find the departments that have the average salaries of employees between 5,000 and 7,000, you use the AVG function as the following query:

SELECT
  department_name,
  ROUND(AVG(salary), 2) average_salary
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
  department_name
HAVING
  AVG(salary) BETWEEN 5000 AND 7000
ORDER BY
  average_salary;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 department_name | average_salary
-----------------+----------------
 IT              |        5760.00
 Shipping        |        5885.71
 Human Resources |        6500.00

HAVING vs. WHERE #

The WHERE clause applies a condition to rows before the rows are summarized into groups by the GROUP BY clause. However, the HAVING clause applies a condition to the groups after the rows are grouped into groups.

Therefore, it is important to note that the HAVING clause is applied after whereas the WHERE clause is applied before the GROUP BY clause.

Summary #

• Use the HAVING clause to specify a condition for filtering groups.

Databases #

• PostgreSQL HAVING clause
• Oracle HAVING clause
• SQL Server HAVING clause
• MySQL HAVING clause
• SQLite HAVING clause
• Db2 HAVING clause
• MariaDB HAVING clause

Quiz #