Filtering is one of the key operations with data, and SQL offers a number of techniques to filter data within queries. One of those techniques—the HAVING clause—is fully covered in this article, from the fundamentals up to advanced usage, complete with practical examples.
In SQL, the HAVING clause is applied to filter the results of GROUP BY queries based on the results of aggregate functions and groupings. It is rather similar to the WHERE clause, except for the fact that WHERE filters individual rows and cannot be used with aggregate functions like COUNT(), SUM(), AVG(), and suchlike.
Here are the main facts about the HAVING clause to keep in mind:
Now let's take a look at the general syntax of a HAVING query.
SELECT column_list
FROM table_name
WHERE where_conditions
GROUP BY column_list
HAVING having_conditions
ORDER BY order_expression;
In this syntax:
column_list comprises the column(s) you want to
select; it may as well include aggregate functions (e.g.,
SUM(), COUNT(), AVG()) applied to the specified columns.
table_name is the name of the table that the
selected columns belong to.
where_conditions are used to filter the results.
column_list (after GROUP BY) comprises the
column(s) to group the results by.
having_conditions are used to filter grouped
results based on aggregate functions.
order_expression is used to order the results.
The HAVING clause is typically used with the following aggregate functions:
Before we proceed to practical examples, let's take a brief look at the tool that we'll use to show them. It's a superior alternative to the well-known SSMS, and it's called dbForge Studio for SQL Server. The Studio is a high-end IDE that covers multiple aspects of SQL Server development, management, and administration—including, but not limited to the following:
You can also check our feature comparison video that showcases the features of dbForge Studio that you won't find in SSMS.
As you can see, the Studio boasts a feature set worth switching to, or at the very least worth trying. And since the try-before-you-buy principle is the one we follow, you can download the Studio for a free 30-day trial, install it on your machine, and see all of its capabilities in action.
Now let's proceed to simple practical examples that will show you how the HAVING clause works. For instance, it's an excellent way of finding products, customers, or categories with specific characteristics in aggregated data.
Our first example is very simple. We need to get a list of stores that have a total of more than 1,000 products in stock.
SELECT store_id, SUM(quantity) AS total_quantity
FROM production.stocks
GROUP BY store_id
HAVING SUM(quantity) > 1000;
Let's run this query in dbForge Studio for SQL Server.
Thus, we get a list of store IDs alongside the quantity of products.
And what if we need to list brands that offer more than five different product types? Here's how it's done using the COUNT() function.
SELECT brand_id, COUNT(product_id) AS product_count
FROM production.products
GROUP BY brand_id
HAVING COUNT(product_id) > 5;
Now, here's what we get if we run this query in the Studio.
Now, what if we would like to list all products with an average price exceeding $500? The following query with the AVG() function will help us.
SELECT category_id, AVG(list_price) AS average_price
FROM production.products
GROUP BY category_id
HAVING AVG(list_price) > 500;
And we've got the result.
Okay, how about listing products that were sold with a minimum discount that exceeds 10%? Additionally, we don't need repeated product names on our list.
SELECT DISTINCT(SELECT p.product_name
FROM production.products p
WHERE p.product_id = oi.order_id) AS product_name,
MIN(oi.discount) AS min_discount
FROM sales.order_items oi
GROUP BY oi.order_id
HAVING MIN(oi.discount) > 0.1;
Let's see what we'll get if we run it in the Studio.
Similarly, the MAX() function will help us get a list of employees that handled orders with a maximum discount that equals 20%.
SELECT s.first_name, s.last_name,
MAX(oi.discount) AS max_discount
FROM sales.order_items oi
JOIN sales.orders o ON o.order_id = oi.order_id
JOIN sales.staffs s ON o.staff_id = s.staff_id
GROUP BY s.first_name, s.last_name
HAVING MAX(oi.discount) = 0.2;
The output is as follows.
Everything's been perfect so far. Now, let's go back to the WHERE clause, which, as we mentioned, filters individual rows. But can it be used alongside HAVING to make your filtering even more flexible? Well, surely.
For instance, there can be cases when you need to exclude individual rows from groups (that's where you use a WHERE clause) before applying a certain condition to those groups as a whole (that's where you use a HAVING clause).
As a result, you get the following:
Note that you can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in your aggregate function.
In our example, we'll get a list of orders that include more than 5,000 products in total.
SELECT o.order_id, SUM(oi.quantity * p.list_price) AS total_value
FROM sales.order_items oi
JOIN sales.orders o ON o.order_id = oi.order_id
JOIN production.products p ON oi.product_id = p.product_id
GROUP BY o.order_id
HAVING SUM(oi.quantity * p.list_price) > 5000;
Let's run it.
That's it. Now let's proceed to a few more cases involving the HAVING clause.
The usage of HAVING is not limited to a single condition. Multiple conditions can be applied just as well—and to link them, you can use the following logical operators:
As usual, let's proceed to an example. Here's a query that returns a list of stores that have a total of less than 2,000 yet more than 500 products in stock.
SELECT store_id, SUM(quantity) AS total_quantity
FROM production.stocks
GROUP BY store_id
HAVING SUM(quantity) > 500 OR SUM(quantity) < 2000;
Let's run this query in the Studio.
Similarly, you can build conditions with AND and NOT.
A nested query is a query where a complete SELECT statement appears within the HAVING clause (or the WHERE clause, for that matter) of another query. This SELECT is an inner query that is executed first. The result set is then used by the outer query.
Let's take a look at an example of a nested query, which provides the names of employees who processed more than 5 orders with a total worth of goods exceeding 10,000.
SELECT (SELECT s.last_name FROM sales.staffs s WHERE s.staff_id = subquery.staff_id) StaffLastName,
COUNT(subquery.order_id) AS order_count, SUM(subquery.total_value) AS total_sales
FROM (
SELECT o.staff_id, oi.order_id, SUM(oi.quantity * p.list_price) AS total_value
FROM sales.order_items oi
JOIN sales.orders o ON o.order_id = oi.order_id
JOIN production.products p ON oi.product_id = p.product_id
GROUP BY o.staff_id, oi.order_id
) AS subquery
GROUP BY staff_id
HAVING COUNT(subquery.order_id) > 5 AND SUM(subquery.total_value) > 10000
ORDER BY SUM(subquery.total_value) DESC;
Okay, let's run it.
We use cookies to provide you with a better experience on the Devart website. You can read more about our use of cookies in our Cookies Policy.
Click OK to continue browsing the Devart site. Be aware you can disable cookies at any time.