The HAVING
clause was added to SQL because the WHERE
keyword cannot be used with aggregate functions. The HAVING
clause is used in the SELECT
statement to specify filter conditions for a group of rows or aggregates.
The HAVING
clause is often used with the GROUP BY
clause to filter groups based on a specified condition. If you omit the GROUP BY
clause, the HAVING
clause behaves like the WHERE
clause.
HAVING Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
In this syntax, you specify a condition in the HAVING
clause.
The HAVING clause evaluates each group returned by the GROUP BY clause. If the result is true, the row is included in the result set. Notice that the HAVING
clause applies a filter condition to each group of rows, while the WHERE
clause applies the filter condition to each individual row. MySQL evaluates the HAVING
clause after the FROM
, WHERE
, SELECT
and GROUP BY
clauses and before ORDER BY
, and LIMIT
clauses:
Note that the SQL standard specifies that the HAVING
is evaluated before SELECT
clause and after GROUP BY
clause.
Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
MySQL HAVING Examples
The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:
Example
SELECT COUNT(CustomerID), Country<br>FROM Customers<br>GROUP BY Country<br>HAVING COUNT(CustomerID) > 5;
The following SQL statement lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers):
Example
SELECT COUNT(CustomerID), Country<br>FROM Customers<br>GROUP BY Country<br>HAVING COUNT(CustomerID) > 5<br>ORDER BY COUNT(CustomerID) DESC;
Demo Database
Below is a selection from the “Orders” table in the Northwind sample database:
OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
---|---|---|---|---|
10248 | 90 | 5 | 1996-07-04 | 3 |
10249 | 81 | 6 | 1996-07-05 | 1 |
10250 | 34 | 4 | 1996-07-08 | 2 |
And a selection from the “Employees” table:
EmployeeID | LastName | FirstName | BirthDate | Photo | Notes |
---|---|---|---|---|---|
1 | Davolio | Nancy | 1968-12-08 | EmpID1.pic | Education includes a BA…. |
2 | Fuller | Andrew | 1952-02-19 | EmpID2.pic | Andrew received his BTS…. |
3 | Leverling | Janet | 1963-08-30 | EmpID3.pic | Janet has a BS degree…. |
More HAVING Examples
The following SQL statement lists the employees that have registered more than 10 orders:
Example
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders<br>FROM (Orders<br>INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)<br>GROUP BY LastName<br>HAVING COUNT(Orders.OrderID) > 10;
The following SQL statement lists if the employees “Davolio” or “Fuller” have registered more than 25 orders:
Example
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders<br>FROM Orders<br>INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID<br>WHERE LastName = 'Davolio' OR LastName = 'Fuller'<br>GROUP BY LastName<br>HAVING COUNT(Orders.OrderID) > 25;
More MySQL HAVING clause examples
Let’s take some examples of using the HAVING
clause to see how it works. We’ll use the orderdetails
table in the sample database for the demonstration.

The following uses the GROUP BY
clause to get order numbers, the number of items sold per order, and total sales for each from the orderdetails
table:
<code>SELECT ordernumber, SUM(quantityOrdered) AS itemsCount, SUM(priceeach*quantityOrdered) AS total FROM orderdetails GROUP BY ordernumber;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Now, you can find which order has total sales greater than 1000
by using the HAVING
clause as follows:
<code>SELECT ordernumber, SUM(quantityOrdered) AS itemsCount, SUM(priceeach*quantityOrdered) AS total FROM orderdetails GROUP BY ordernumber HAVING total > 1000;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

It’s possible to form a complex condition in the HAVING
clause using logical operators such as OR
and AND
.
The following example uses the HAVING
clause to find orders that have total amounts greater than 1000
and contain more than 600
items:
<code>SELECT ordernumber, SUM(quantityOrdered) AS itemsCount, SUM(priceeach*quantityOrdered) AS total FROM orderdetails GROUP BY ordernumber HAVING total > 1000 AND itemsCount > 600;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Suppose that you want to find all orders that already shipped and have a total amount greater than 1500, you can join the orderdetails
table with the orders
table using the INNER JOIN
clause and apply a condition on status
column and total
aggregate as shown in the following query:
<code>SELECT a.ordernumber, status, SUM(priceeach*quantityOrdered) total FROM orderdetails a INNER JOIN orders b ON b.ordernumber = a.ordernumber GROUP BY ordernumber, status HAVING status = 'Shipped' AND total > 1500;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

The HAVING
clause is only useful when you use it with the GROUP BY
clause to generate the output of the high-level reports. For example, you can use the HAVING
clause to answer the questions like finding the number of orders this month, this quarter, or this year that have a total amount greater than 10K.