MySQL HAVING

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:

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

Display:

Now you can ask what order has total value greater than $1600. 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 > 1600

Display:

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

Display

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 1600 this month, this quarter and this year?…

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 10 items in it. You can use the following query to find out: