SQL HAVING Tutorial

Learn how to use the SQL HAVING clause to filter groups using your own specified conditions.

The SQL HAVING clause is often a challenging concept to grasp, especially for beginners. Additionally, its specific use cases can be difficult to visualize without specific examples based on real-world datasets. In this tutorial, you will learn how to apply the SQL HAVING clause to filter groups of records based on specified conditions. You’ll see examples where we apply this concept to solving business problems on a realistic dataset.

The Function of the SQL HAVING Clause

The SQL HAVING clause is typically used with the GROUP BY clause to filter groups of returned rows. Only when the specified condition is TRUE are rows included in the group.

The SQL GROUP BY clause arranges data into groups based on common value(s); it’s most often used to obtain summary information and compute aggregate statistics. If you are looking for a more in-depth refresher on this subject, see this article on SQL GROUP BY.

There might be some confusion about WHERE and HAVING, but the difference is easily explained. The WHERE clause applies a condition on the entire column by filtering individual rows. It does not work with aggregate functions such as SUM() and AVG(). On the other hand, HAVING puts filter conditions on the groups created by the GROUP BY clause. It can be used with aggregate functions.

For example, let’s consider the table below. It shows a list of customers grouped by cities across multiple countries. If we wanted to see groups of cities with 3 or more customers, we’d need a HAVING clause that satisfies this condition on each of the cities (i.e. the groups).

In other words, the HAVING clause is essentially a WHERE clause that operates on records returned by GROUP BY. (We also have a more in-depth comparison between HAVING and WHERE if you're interested in going deeper.)

SQL HAVING Syntax

HAVING won’t work without the GROUP BY clause. It must follow the GROUP BY clause in a query and must precede the ORDER BY clause (if you’re using one). Only groups that meet the HAVING criteria will be returned.

For example, let’s try the following query on the customers table:

SELECT COUNT(CustomerID), City, Country
FROM Customers
WHERE Country = 'Brazil'
GROUP BY City, Country
HAVING COUNT(CustomerID) > 1

What’s going on in this query?

• First, the WHERE clause filters the applicable rows for the Country “Brazil”.
• Second, the GROUP BY clause groups entries based on City and Country column values.
• Third, the HAVING clause filters the groups – in this case, we are only interested in Brazilian cities that have more than 1 customer.

The LearnSQL.com SQL Basics course covers the syntax and application of the HAVING clause in an engaging and interactive environment. If you want hands-on practice, this is my recommendation. But first, let’s look at more examples of HAVING.

SQL HAVING Examples

Let’s dive into some examples featuring different aggregate functions so we can understand how the HAVING clause really works. We will be using the following customer OrderDetails table. It has a total of 518 rows of straightforward order data. This includes information such as the Quantity and ProductID ordered along with a unique OrderID for each order.

As a data analyst, one of your key duties would be to obtain insights into sales data. We’ll explore three simple problems aimed at finding some of those insights.

Example 1: HAVING with COUNT()

PROBLEM: List all products with more than 12 individual orders placed.

QUERY:

SELECT COUNT(ProductID), ProductID
FROM OrderDetails
GROUP BY ProductID
HAVING COUNT(ProductID) > 12

RESULT:

Number of Records: 5

If we hadn’t included the HAVING clause, this query would essentially return the number of records equal to the unique number of ProductIDs. Here, GROUP BY puts orders with the same ProductID into groups. Next, the HAVING clause places the filtering condition on each of those groups and returns the ones that have a count greater than 12.

Example 2: HAVING with SUM()

PROBLEM: List all products that have had over 350 total quantities ordered.

QUERY:

SELECT SUM(Quantity), ProductID
FROM OrderDetails
GROUP BY ProductID
HAVING SUM(Quantity) > 350
ORDER BY SUM(Quantity) DESC

RESULT:

Number of Records: 3

In the example above, GROUP BY similarly groups the orders by ProductID. Then, the HAVING clause places the filtering condition (the sum of the Quantity column should be over 350) on each of those groups. Finally, ORDER BY returns the results in descending order.

Example 3: HAVING with AVG()

PROBLEM: List all products which have had over 35 quantities ordered on average and at least 4 orders placed.

QUERY:

SELECT AVG(Quantity), COUNT(ProductID), ProductID
FROM OrderDetails
GROUP BY ProductID
HAVING AVG(Quantity) > 35 AND COUNT(ProductID) > 3
ORDER BY AVG(Quantity) DESC

RESULT:

Number of Records: 6

In Example 3, after we group the orders by ProductID, the HAVING clause places two filtering conditions on the groups: an average order quantity over 35 and a number of individual orders greater than 3.

Based on the three examples above, we have learned that ProductID 31, for example, was included in the highest number of individual orders (14) and also had the greatest number of ordered quantities across all orders placed (458). This definitely is one of the more popular products in the store!

Additionally, ProductIDs 23, 35, and 58 had the highest average quantities per order. Key insights such as these are valuable, letting businesses know which products to focus on and how to market them.

Mastering the SQL HAVING Clause

As we’ve just seen, the HAVING clause is indispensable for calculating aggregate values on groups of data. Thus, it’s always used in combination with a GROUP BY clause and restricts data on the grouped records rather than on individual records.

If you want to learn more about using the SQL HAVING clause, try our SQL Basics course. It covers the HAVING clause in detail and explains more about grouping, filtering, and ordering data. There are also a lot of practice problems and quizzes so you can get really comfortable using basic SQL.