{"id":214,"date":"2011-05-07T09:51:59","date_gmt":"2011-05-07T09:51:59","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=214"},"modified":"2024-06-09T21:03:44","modified_gmt":"2024-06-10T04:03:44","slug":"mysql-having","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-having\/","title":{"rendered":"MySQL HAVING"},"content":{"rendered":"\n

Summary<\/strong>: <\/em>in this tutorial, you will learn how to use MySQL HAVING<\/code> clause to specify a filter condition for groups of rows or aggregates.<\/p>\n\n\n\n

Introduction to MySQL HAVING clause<\/h2>\n\n\n\n

The HAVING<\/code> clause is used with the GROUP BY<\/a> clause to filter the groups based on a specified condition.<\/p>\n\n\n\n

The HAVING<\/code> clause allows you to apply a condition to the groups returned by the GROUP BY<\/code> clause and only include groups that meet the specified condition.<\/p>\n\n\n\n

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

SELECT<\/span> \n    select_list\nFROM<\/span> \n    table_name\nWHERE<\/span> \n    search_condition\nGROUP<\/span> BY<\/span> \n    group_by_expression\nHAVING<\/span> \n    group_condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify a condition in the HAVING<\/code> clause. <\/p>\n\n\n\n
The HAVING<\/code> clause evaluates each group returned by the GROUP BY<\/code> clause. If the result is true (1), it includes the group in the result set.<\/p>\n\n\n\n
The HAVING<\/code> clause applies the condition to groups of rows, while the WHERE<\/code> clause applies the condition to individual rows<\/p>\n\n\n\n
MySQL evaluates the HAVING<\/code> clause after the FROM<\/code>, WHERE<\/a><\/code>, SELECT<\/code> and GROUP BY<\/a><\/code> clauses, but before DISTINCT<\/a><\/code>, SELECT<\/a><\/code>, ORDER BY<\/a><\/code>, and LIMIT<\/a><\/code> clauses:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
Note that the SQL standard specifies that the HAVING<\/code> is evaluated before SELECT<\/code> clause and after GROUP BY<\/code> clause.<\/p>\n\n\n\n
MySQL HAVING clause examples<\/h2>\n\n\n\n
Let’s take some examples of using the HAVING<\/code> clause to understand how it works. <\/p>\n\n\n\n
We’ll use the orderdetails<\/code> table in the sample database<\/a> for the demonstration.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following uses the GROUP BY<\/code> clause to get order numbers, the number of items sold per order, and total sales for each from the orderdetails<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n  ordernumber, \n  SUM<\/span>(quantityOrdered) AS<\/span> itemsCount, \n  SUM<\/span>(priceeach * quantityOrdered) AS<\/span> total \nFROM<\/span> \n  orderdetails \nGROUP<\/span> BY<\/span> \n  ordernumber;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+-------------+------------+----------+<\/span>\n| ordernumber | itemsCount | total    |\n+-------------+------------+----------+<\/span>\n|       10100 |        151 | 10223.83 |\n|       10101 |        142 | 10549.01 |\n|       10102 |         80 |  5494.78 |\n|       10103 |        541 | 50218.95 |\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Now, you can find which order has total sales greater than 1000<\/code> by using the HAVING<\/code> clause as follows:<\/p>\n\n\n
SELECT<\/span> \n  ordernumber, \n  SUM<\/span>(quantityOrdered) AS<\/span> itemsCount, \n  SUM<\/span>(priceeach * quantityOrdered) AS<\/span> total \nFROM<\/span> \n  orderdetails \nGROUP<\/span> BY<\/span> \n  ordernumber \nHAVING<\/span> \n  total > 1000<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+-------------+------------+----------+<\/span>\n| ordernumber | itemsCount | total    |\n+-------------+------------+----------+<\/span>\n|       10100 |        151 | 10223.83 |\n|       10101 |        142 | 10549.01 |\n|       10102 |         80 |  5494.78 |\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example uses the HAVING<\/code> clause to find orders that have total amounts greater than 1000<\/code> and contain more than 600<\/code> items:<\/p>\n\n\n
SELECT<\/span> \n    ordernumber,\n    SUM<\/span>(quantityOrdered) AS<\/span> itemsCount,\n    SUM<\/span>(priceeach*quantityOrdered) AS<\/span> total\nFROM<\/span>\n    orderdetails\nGROUP<\/span> BY<\/span> ordernumber\nHAVING<\/span> \n    total > 1000<\/span> AND<\/span> \n    itemsCount > 600<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
Suppose that you want to find all orders that already shipped and have a total amount greater than 1500, you can join<\/a> the orderdetails<\/code> table with the orders<\/code> table using the INNER JOIN<\/a><\/code> clause and apply a condition on status<\/code> column and total<\/code> aggregate as shown in the following query:<\/p>\n\n\n
SELECT<\/span> \n    a.ordernumber, \n    status<\/span>, \n    SUM<\/span>(priceeach*quantityOrdered) total\nFROM<\/span>\n    orderdetails a\nINNER<\/span> JOIN<\/span> orders b \n    ON<\/span> b.ordernumber = a.ordernumber\nGROUP<\/span> BY<\/span>  \n    ordernumber, \n    status<\/span>\nHAVING<\/span> \n    status<\/span> = 'Shipped'<\/span> AND<\/span> \n    total > 1500<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The HAVING<\/code> clause is only useful when you use it with the GROUP BY<\/code> clause to generate the output of the high-level reports. <\/p>\n\n\n\n
For example, you can use the HAVING<\/code> clause to answer questions like finding the number of orders this month, this quarter, or this year that have a total amount greater than 10K.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL HAVING<\/code> clause with the GROUP BY<\/code> clause to specify a filter condition for groups of rows or aggregates.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
It’s possible to form a complex condition in the HAVING<\/code> clause using logical operators such as OR<\/a><\/code> and AND<\/a><\/code>.<\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
If you omit the GROUP BY<\/code> clause, the HAVING<\/code> clause behaves like the WHERE<\/a><\/code> clause.<\/p>\n\n\n\n