MySQL HAVING Clause with Examples

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_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 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().


See also:
MySQL LIKE Operator Pattern Matching and Examples
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function | Concatenate Strings in MySQL

Leave a Comment