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 FROMWHERESELECT and GROUP BY clauses and before ORDER BY, and LIMIT clauses:

MySQL Having

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:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1
Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico
4
Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK
5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Sweden

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:

OrderIDCustomerIDEmployeeIDOrderDateShipperID
102489051996-07-043
102498161996-07-051
102503441996-07-082

And a selection from the “Employees” table:

EmployeeIDLastNameFirstNameBirthDatePhotoNotes
1DavolioNancy1968-12-08EmpID1.picEducation includes a BA….
2FullerAndrew1952-02-19EmpID2.picAndrew received his BTS….
3LeverlingJanet1963-08-30EmpID3.picJanet 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&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

MySQL HAVING - GROUP BY example

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

MySQL HAVING example

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

MySQL HAVING with INNER JOIN example

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.

MySQL HAVING Clause
Show Buttons
Hide Buttons