MySQL HAVING Clause with Examples


How to Use the MySQL HAVING Clause

In this tutorial, we'll learn how to use the MySQL HAVING clause. The HAVING clause is used to filter groups based on a specified condition.

We often use the HAVING clause 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 behaves like the WHERE clause when the GROUP BY clause is omitted.
  • While the WHERE clause applies filter conditions to each individual record, the HAVING clause applies a filter to each group of records.
  • We often use the HAVING clause with aggregate functions, such as SUM() or COUNT().

Examples

Below is the demo orders table:

order_idcustomer_codecustomer_nameorder_numberorder_dateamount
1#CID_30010Lisa Ramsey#ORD_100102019-05-131980.00
2#CID_30010Lisa Ramsey#ORD_100112019-05-151350.50
3#CID_30011Iris Wilkerson#ORD_100122019-05-071230.40
4#CID_30013Clifton Christensen#ORD_100132019-05-231200.00
5#CID_30011Iris Wilkerson#ORD_100142019-05-313450.00
6#CID_30013Clifton Christensen#ORD_100152019-06-023470.50
7#CID_30016Francis Lewis#ORD_100162019-06-031230.80
8#CID_30017William Barnett#ORD_100172019-06-061998.00

Example 1: HAVING Behaves Like WHERE

The following statement selects all orders placed 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 not used in this statement. So, the HAVING clause behaves like the WHERE clause.

Example 2: HAVING with SUM()

The following statement sums all the amount of orders for each customer, and returns only records that 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;

This example illustrated how to use the HAVING clause with the SUM() function.

Example 3: HAVING with COUNT()

The following statement counts the number of orders for each customer, and returns only records that the number of orders is greater or equal to 2:

SELECT o.customer_code, o.customer_name, COUNT(o.order_id) AS NumberOfOrders
FROM orders o
GROUP BY o.customer_code
HAVING NumberOfOrders >= 2;

This example shows you how to use the HAVING clause with the COUNT() function.

Example 4: HAVING with COUNT() and SUM()

The following statement sums all the amount, counts the number of orders, and returns only records that the number of orders is equal to 1 and 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.

In this tutorial, we've learned how to use the MySQL HAVING clause. The HAVING clause is used to specify filter conditions for a group of records. It can used with the GROUP BY clause, and often used with aggregate functions such as SUM() or COUNT().

Leave a Reply

Your email address will not be published. Required fields are marked *