MySQL HAVING Clause

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:

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:

And a selection from the “Employees” table:

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&nbsp;Employees.LastName,&nbsp;COUNT(Orders.OrderID)&nbsp;AS&nbsp;NumberOfOrders<br>FROM&nbsp;Orders<br>INNER&nbsp;JOIN&nbsp;Employees&nbsp;ON&nbsp;Orders.EmployeeID = Employees.EmployeeID<br>WHERE&nbsp;LastName =&nbsp;'Davolio'&nbsp;OR&nbsp;LastName =&nbsp;'Fuller'<br>GROUP&nbsp;BY&nbsp;LastName<br>HAVING&nbsp;COUNT(Orders.OrderID) &gt;&nbsp;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>

Try It Out

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 &gt; 1000;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Try It Out

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 &gt; 1000 AND 
    itemsCount &gt; 600;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Try It Out

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 &gt; 1500;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Try It Out

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.