MySQL HAVING

Summary: in this tutorial, you will learn how to use MySQL HAVING clause to specify a filter condition for groups of rows or aggregates.

Introduction to MySQL HAVING clause

The HAVING clause is used with the GROUP BY clause to filter the groups based on a specified condition.

The HAVING clause allows you to apply a condition to the groups returned by the GROUP BY clause and only include groups that meet the specified condition.

Here’s the syntax of the HAVING clause:

SELECT 
    select_list
FROM 
    table_name
WHERE 
    search_condition
GROUP BY 
    group_by_expression
HAVING 
    group_condition;Code language: SQL (Structured Query Language) (sql)

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 (1), it includes the group in the result set.

The HAVING clause applies the condition to groups of rows, while the WHERE clause applies the condition to individual rows

If you omit the GROUP BY clause, the HAVING clause behaves like the WHERE clause.

MySQL evaluates the HAVING clause after the FROM, WHERE, SELECT and GROUP BY clauses, but before DISTINCT, SELECT, ORDER BY, and LIMIT clauses:

Note that the SQL standard specifies that the HAVING is evaluated before SELECT clause and after GROUP BY clause.

MySQL HAVING clause examples

Let’s take some examples of using the HAVING clause to understand 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:

SELECT 
  ordernumber, 
  SUM(quantityOrdered) AS itemsCount, 
  SUM(priceeach * quantityOrdered) AS total 
FROM 
  orderdetails 
GROUP BY 
  ordernumber;Code language: SQL (Structured Query Language) (sql)

Try It Out

+-------------+------------+----------+
| ordernumber | itemsCount | total    |
+-------------+------------+----------+
|       10100 |        151 | 10223.83 |
|       10101 |        142 | 10549.01 |
|       10102 |         80 |  5494.78 |
|       10103 |        541 | 50218.95 |
...Code language: SQL (Structured Query Language) (sql)

Now, you can find which order has total sales greater than 1000 by using the HAVING clause as follows:

SELECT 
  ordernumber, 
  SUM(quantityOrdered) AS itemsCount, 
  SUM(priceeach * quantityOrdered) AS total 
FROM 
  orderdetails 
GROUP BY 
  ordernumber 
HAVING 
  total > 1000;Code language: SQL (Structured Query Language) (sql)

Try It Out

+-------------+------------+----------+
| ordernumber | itemsCount | total    |
+-------------+------------+----------+
|       10100 |        151 | 10223.83 |
|       10101 |        142 | 10549.01 |
|       10102 |         80 |  5494.78 |
...Code language: SQL (Structured Query Language) (sql)

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:

SELECT 
    ordernumber,
    SUM(quantityOrdered) AS itemsCount,
    SUM(priceeach*quantityOrdered) AS total
FROM
    orderdetails
GROUP BY ordernumber
HAVING 
    total > 1000 AND 
    itemsCount > 600;Code language: SQL (Structured Query Language) (sql)

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:

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 language: SQL (Structured Query Language) (sql)

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 questions like finding the number of orders this month, this quarter, or this year that have a total amount greater than 10K.

Summary

• Use the MySQL HAVING clause with the GROUP BY clause to specify a filter condition for groups of rows or aggregates.