SQL HAVING

Summary: this tutorial introduces you to the SQL HAVING clause that allows you to specify a condition for the groups summarized by the GROUP BY clause.

Introduction to SQL HAVING clause

In the previous tutorial, you have learned how to use the GROUP BY clause to summarize rows into groups and apply the aggregate function such as MIN, MAX, SUM, COUNT, AVG to each group.

To specify a condition for groups, you use the HAVING clause.

The HAVING clause is often used with the GROUP BY clause in the SELECT statement. If you use a HAVING clause without a GROUP BY clause, the HAVING clause behaves like the WHERE clause.

The following illustrates 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.

HAVING vs. WHERE

The WHERE clause applies the condition to individual rows before the rows are summarized into groups by the GROUP BY clause. However, the HAVING clause applies the 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.

SQL HAVING clause examples

We will take the employees and departments tables in 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.

The following query illustrates the idea:

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

Try It

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,
    first_name,
    last_name,
    COUNT(employee_id) direct_reports
FROM
    employees
WHERE
    manager_id IS NOT NULL
GROUP BY manager_id
HAVING direct_reports >= 5;Code language: SQL (Structured Query Language) (sql)

Try It

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)
FROM
    employees
GROUP BY department_id
HAVING SUM(salary) BETWEEN 20000 AND 30000
ORDER BY SUM(salary);Code language: SQL (Structured Query Language) (sql)

Try It

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
	e.department_id,
	department_name,
	MIN(salary)
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	e.department_id
HAVING
	MIN(salary) >= 10000
ORDER BY
	MIN(salary);Code language: SQL (Structured Query Language) (sql)

Try It

How the query works.

1. First, use the GROUP BY clause to groups 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 5000 and 7000, you use the AVG function as the following query:

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

Try It

In this tutorial, you have learned how to use the SQL HAVING clause to apply the condition to groups.