» Basic MySQL Tutorial » MySQL HAVING

MySQL HAVING

Summary: In this tutorial, you will learn how to use MySQL HAVING clause to specify a filter condition for a group of records or an aggregate.

Introducing MySQL HAVING clause

The MySQL HAVING clause is an optional part of and used only with the SQL SELECT statement. The MySQL HAVING clause specifies a filter condition for a group of record or an aggregate. The MySQL HAVING is often used with MySQL GROUP BY clause. When using with MYSQL GROUP BY clause, you can apply filter condition of the HAVING clause only to the columns appear in the GROUP BY clause. If the MySQL GROUP BY clause is omitted, the MySQL HAVING clause will behave like a WHERE clause. Notes that the MySQL HAVING clause applies to groups as a whole while the WHERE clause applies to individual rows.

Examples of MySQL HAVING clause

Let’s take a look at an example of using MySQL HAVING clause to have a better understanding.

We have orderDetails table in our sample database. We can use the MySQL GROUP BY clause to get all orders, number of items sold and total values in each order as follows:

MySQL HAVING - Sample Table

SELECT ordernumber,
       sum(quantityOrdered) AS itemsCount,
       sum(priceeach) AS total
FROM orderdetails
GROUP BY ordernumber

MySQL HAVING Example

Now you can ask what order has total value greater than $1000. In this case, you need to use the MySQL HAVING clause on aggregate to answer that question.

SELECT ordernumber,
       sum(quantityOrdered) AS itemsCount,
       sum(priceeach) AS total
FROM orderdetails
GROUP BY ordernumber
HAVING total > 1000

MySQL HAVING Example 2

We use column alias for the aggregate sum(priceeach) as total so in the HAVING clause we just have to specify that column alias total instead of typing the aggregate sum(priceeach) again.

You can use a complex condition in the MySQL HAVING clause such as OR, AND operators. For example if you want to know what order has total value greater than $1000 and has more than 600 items in it. You can use the following query to find out:

SELECT ordernumber,
       sum(quantityOrdered) AS itemsCount,
       sum(priceeach) AS total
FROM orderdetails
GROUP BY ordernumber
HAVING total > 1000 AND itemsCount > 600

MySQL HAVING Example 3

The MySQL HAVING clause is useful only with the MySQL GROUP BY clause for building output of high-level reports. For example, you can use the MySQL HAVING clause to answer questions like how many order has total values more than 1000 this month, this quarter and this year?...

In this tutorial, you have learned how to use the MySQL HAVING clause together with the MySQL GROUP BY to specify filter condition on a group or aggregate.

Tutorials Like MySQL HAVING
Search