How to Use the MySQL HAVING Clause
In this tutorial, you will learn how to use the MySQL HAVING clause, which is used to filter groups based on specified conditions.
It is common to use the HAVING clause together with the GROUP BY clause.
MySQL HAVING Syntax
The syntax of the HAVING clause can be as follows:
SELECT column_name(s)
FROM table_name
WHERE where_condition
GROUP BY column_name(s)
HAVING having_condition;
Remarks
- The HAVING clause works the same way as the WHERE clause when the GROUP BY clause is omitted.
- While the WHERE clause applies the filter condition to individual rows, the HAVING clause applies the filter to each group of rows.
- The HAVING clause is often used with aggregate functions such as SUM() and COUNT().
Examples
Below is the "orders" table for our examples.
order_id | customer_code | customer_name | order_number | order_date | amount |
---|---|---|---|---|---|
1 | #CID_30010 | Lisa Ramsey | #ORD_10010 | 2019-05-13 | 1980.00 |
2 | #CID_30010 | Lisa Ramsey | #ORD_10011 | 2019-05-15 | 1350.50 |
3 | #CID_30011 | Iris Wilkerson | #ORD_10012 | 2019-05-07 | 1230.40 |
4 | #CID_30013 | Clifton Christensen | #ORD_10013 | 2019-05-23 | 1200.00 |
5 | #CID_30011 | Iris Wilkerson | #ORD_10014 | 2019-05-31 | 3450.00 |
6 | #CID_30013 | Clifton Christensen | #ORD_10015 | 2019-06-02 | 3470.50 |
7 | #CID_30016 | Francis Lewis | #ORD_10016 | 2019-06-03 | 1230.80 |
8 | #CID_30017 | William Barnett | #ORD_10017 | 2019-06-06 | 1998.00 |
Example 1: HAVING behaves like WHERE clause
The following SQL query selects all orders made between 2019-05-01 and 2019-05-20:
SELECT * FROM orders o
HAVING o.order_date BETWEEN "2019-05-01" AND "2019-05-20";
As you can see, the GROUP BY clause is omitted in this SQL query, so the HAVING clause works the same way as the WHERE clause.
Example 2: HAVING clause and SUM() function
In this example, I show how we use the HAVING clause with the SUM() function.
The following SQL query sums up all the order amounts for each customer and returns only those rows where the total amount is greater than 2000.
SELECT o.customer_code, o.customer_name, SUM(o.amount) AS TotalAmount
FROM orders o
GROUP BY o.customer_code
HAVING TotalAmount > 2000;
Example 3: HAVING clause with COUNT() function
This example shows how to use the HAVING clause with the COUNT() function.
The following SQL query counts the number of orders for each customer and returns only rows with an order count of 2 or more:
SELECT o.customer_code, o.customer_name, COUNT(o.order_id) AS NumberOfOrders
FROM orders o
GROUP BY o.customer_code
HAVING NumberOfOrders >= 2;
Example 4: HAVING with COUNT() and SUM()
The following SQL query sums up all the amounts, counts the number of orders, and returns only the rows where the number of orders is 1; and the total amount is greater than 1500:
SELECT o.customer_code, o.customer_name, SUM(o.amount) AS TotalAmount, COUNT(o.customer_code) AS NumberOfOrders
FROM orders o
GROUP BY o.customer_code
HAVING NumberOfOrders = 1 AND TotaLAmount > 1500;
This example illustrated how to use the HAVING clause with both SUM () and COUNT() functions.
Summary
In this tutorial, you have learned how to use the HAVING clause in MySQL.
The HAVING clause is used to specify filter conditions for a group of records; it can be used with the GROUP BY clause and is often used with aggregate functions such as SUM() and COUNT().