Summary: in this tutorial, you will learn how to use MySQL HAVING clause to specify a filter condition for groups of rows or aggregates.
Introducing MySQL HAVING clause
The MySQL HAVING clause is used in the SELECT statement to specify filter conditions for group of rows or aggregates.
The MySQL HAVING clause is often used with the GROUP BY clause. When using with the GROUP BY clause, you can apply a filter condition to the columns that appear in the GROUP BY clause. If the GROUP BY clause is omitted, the MySQL HAVING clause behaves like the WHERE clause. Notice that the MySQL HAVING clause applies the condition to each group of rows, while the WHERE clause applies the condition to each individual row.
Examples of using MySQL HAVING clause
Let’s take a look at an example of using MySQL HAVING clause.
We will use the orderdetails table in the sample database for the sake of demonstration.
We can use the MySQL GROUP BY clause to get order number, the number of items sold per order and total sales for each:
SELECT ordernumber, SUM(quantityOrdered) AS itemsCount, SUM(priceeach) AS total FROM orderdetails GROUP BY ordernumber
Now, we can find which order has total sales greater than $1000. We use the MySQL HAVING clause on the aggregate as follows:
SELECT ordernumber, SUM(quantityOrdered) AS itemsCount, SUM(priceeach) AS total FROM orderdetails GROUP BY ordernumber HAVING total > 1000
We can construct a complex condition in the MySQL HAVING clause using logical operators such as OR and AND. Suppose we want to find which order has total sales greater than $1000 and contains more than 600 items, we can use the following query:
SELECT ordernumber, sum(quantityOrdered) AS itemsCount, sum(priceeach) AS total FROM orderdetails GROUP BY ordernumber HAVING total > 1000 AND itemsCount > 600
Suppose we want to find all orders that has shipped and has total sales greater than $1500, we can join the orderdetails table with the orders table by using the INNER JOIN clause, and apply condition on the statuscolumn and the total aggregate as the following query:
SELECT a.ordernumber, SUM(priceeach) total, status FROM orderdetails a INNER JOIN orders b ON b.ordernumber = a.ordernumber GROUP BY ordernumber HAVING b.status = 'Shipped' AND total > 1500;
The MySQL HAVING clause is only useful when we use it with the GROUP BY clause to generate the output of the high-level reports. For example, we can use the MySQL HAVING clause to answer some kinds of queries like give me all the orders in this month, this quarter and this year that have total sales greater than 10K.
In this tutorial, you have learned how to use the MySQL HAVING clause together with the GROUP BY to specify filter condition on groups of records or aggregates.
随时随地看视频
