{"id":194,"date":"2016-03-25T09:50:14","date_gmt":"2016-03-25T09:50:14","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=194"},"modified":"2025-02-04T07:29:20","modified_gmt":"2025-02-04T14:29:20","slug":"sql-having","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-having\/","title":{"rendered":"SQL HAVING"},"content":{"rendered":"\r\n

Summary<\/strong>: In this tutorial, you’ll learn how to use the SQL HAVING<\/code> clause to filter groups based on a condition.<\/p>\r\n\r\n\r\n\r\n

Introduction to SQL HAVING clause #<\/a><\/h2>\r\n\r\n\r\n\r\n

The GROUP BY<\/a> clause groups rows of a result set into groups. To specify a condition for filtering groups, you use a HAVING<\/code> clause.<\/p>\r\n\r\n\r\n\r\n

If you use a HAVING<\/code> clause without a GROUP BY<\/code> clause, the HAVING<\/code> clause behaves like a WHERE clause<\/a>.<\/p>\r\n\r\n\r\n\r\n

Here’s the syntax of the HAVING<\/code> clause:<\/p>\r\n\r\n\r\n

SELECT<\/span>\r\n  column1,\r\n  column2,\r\n  aggregate_function (column3)\r\nFROM<\/span>\r\n  table1\r\nGROUP<\/span> BY<\/span>\r\n  column1,\r\n  column2\r\nHAVING<\/span>\r\n  group_condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Note that the HAVING<\/code> clause appears immediately after the GROUP BY<\/code> clause.<\/p>\r\n\r\n\r\n\r\n
SQL HAVING clause examples #<\/a><\/h2>\r\n\r\n\r\n\r\n
We’ll use the following employees<\/code> and departments<\/code> tables from the sample database<\/a> for the demonstration.<\/p>\r\n\r\n\r\n\r\n
\"employees_dependents_tables\"<\/figure>\r\n\r\n\r\n\r\n
To get the managers and their direct reports, you use the GROUP BY<\/code> clause to group employees by the managers and use the COUNT function<\/a> to count the direct reports:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  manager_id,\r\n  COUNT<\/span>(employee_id) direct_reports\r\nFROM<\/span>\r\n  employees\r\nWHERE<\/span>\r\n  manager_id IS<\/span> NOT<\/span> NULL<\/span>\r\nGROUP<\/span> BY<\/span>\r\n  manager_id\r\nORDER<\/span> BY<\/span>\r\n  direct_reports;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Output:<\/p>\r\n\r\n\r\n
 manager_id | direct_reports\r\n------------+----------------\r\n        201 |              1\r\n        102 |              1\r\n        120 |              1\r\n        205 |              1\r\n        123 |              2\r\n        103 |              4\r\n        101 |              5\r\n        114 |              5\r\n        108 |              5\r\n        100 |             14<\/code><\/span><\/pre>\r\n\r\n\r\n
To find the managers who have at least five direct reports, you add a HAVING<\/code> clause to the query above as the following:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  manager_id,\r\n  COUNT<\/span>(employee_id) direct_reports\r\nFROM<\/span>\r\n  employees\r\nWHERE<\/span>\r\n  manager_id IS<\/span> NOT<\/span> NULL<\/span>\r\nGROUP<\/span> BY<\/span>\r\n  manager_id\r\nHAVING<\/span>\r\n  COUNT<\/span>(employee_id) >= 5<\/span>\r\nORDER<\/span> BY<\/span>\r\n  direct_reports;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Output:<\/p>\r\n\r\n\r\n
 manager_id | direct_reports\r\n------------+----------------\r\n        101 |              5\r\n        114 |              5\r\n        108 |              5\r\n        100 |             14<\/code><\/span><\/pre>\r\n\r\n\r\n
SQL HAVING with SUM function example #<\/a><\/h3>\r\n\r\n\r\n\r\n
The following statement calculates the sum<\/a> of salary that the company pays for each department and selects only the departments with the sum of salary between<\/a> 20000 and 30000.<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  department_id,\r\n  SUM<\/span>(salary) total_salary\r\nFROM<\/span>\r\n  employees\r\nGROUP<\/span> BY<\/span>\r\n  department_id\r\nHAVING<\/span>\r\n  SUM<\/span>(salary) BETWEEN<\/span> 20000<\/span> AND<\/span> 30000<\/span>\r\nORDER<\/span> BY<\/span>\r\n  total_salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Output:<\/p>\r\n\r\n\r\n
 department_id | total_salary\r\n---------------+--------------\r\n            11 |     20300.00\r\n             3 |     24900.00\r\n             6 |     28800.00<\/code><\/span><\/pre>\r\n\r\n\r\n
SQL HAVING with MIN function example #<\/a><\/h3>\r\n\r\n\r\n\r\n
To find the department that has employees with the lowest salary greater than 10000<\/code>, you use the following query:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  department_name,\r\n  MIN<\/span>(salary) min_salary\r\nFROM<\/span>\r\n  employees e\r\n  INNER<\/span> JOIN<\/span> departments d ON<\/span> d.department_id = e.department_id\r\nGROUP<\/span> BY<\/span>\r\n  department_name\r\nHAVING<\/span>\r\n  MIN<\/span>(salary) >= 10000<\/span>\r\nORDER<\/span> BY<\/span>\r\n  MIN<\/span>(salary);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Output:<\/p>\r\n\r\n\r\n
 department_name  | min_salary\r\n------------------+------------\r\n Public<\/span> Relations |   10000.00<\/span>\r\n Executive        |   17000.00<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
How the query works.<\/p>\r\n\r\n\r\n\r\n
    \r\n
  1. First, use the GROUP BY<\/code> clause to group employees by department.<\/li>\r\n\r\n\r\n\r\n
  2. Second, use the MIN<\/a> function to find the lowest salary per group.<\/li>\r\n\r\n\r\n\r\n
  3. Third, apply the condition to the HAVING<\/code> clause.<\/li>\r\n<\/ol>\r\n\r\n\r\n\r\n
    SQL HAVING clause with AVG function example #<\/a><\/h3>\r\n\r\n\r\n\r\n
    To find the departments that have the average salaries of employees between 5,000<\/code> and 7,000<\/code>, you use the AVG<\/a> function as the following query:<\/p>\r\n\r\n\r\n
    SELECT<\/span>\r\n  department_name,\r\n  ROUND<\/span>(AVG<\/span>(salary), 2<\/span>) average_salary\r\nFROM<\/span>\r\n  employees e\r\n  INNER<\/span> JOIN<\/span> departments d ON<\/span> d.department_id = e.department_id\r\nGROUP<\/span> BY<\/span>\r\n  department_name\r\nHAVING<\/span>\r\n  AVG<\/span>(salary) BETWEEN<\/span> 5000<\/span> AND<\/span> 7000<\/span>\r\nORDER<\/span> BY<\/span>\r\n  average_salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    Output:<\/p>\r\n\r\n\r\n
     department_name | average_salary\r\n-----------------+----------------\r\n IT              |        5760.00\r\n Shipping        |        5885.71\r\n Human Resources |        6500.00<\/code><\/span><\/pre>\r\n\r\n\r\n
    HAVING vs. WHERE #<\/a><\/h2>\r\n\r\n\r\n\r\n
    The WHERE<\/a> clause applies a condition to rows before the rows are summarized into groups by the GROUP BY<\/code> clause. However, the HAVING<\/code> clause applies a condition to the groups after the rows are grouped into groups.<\/p>\r\n\r\n\r\n\r\n
    Therefore, it is important to note that the HAVING<\/code> clause is applied after whereas the WHERE<\/code> clause is applied before the GROUP BY<\/code> clause.<\/p>\r\n\r\n\r\n\r\n
    Summary #<\/a><\/h2>\r\n\r\n\r\n\r\n
    Try it<\/a><\/p>\r\n\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n\r\n