SQL HAVING Clause (original) (raw)

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.

employees_dependents_tables

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 #

Databases #

Quiz #

Was this tutorial helpful ?