HAVING Clause: How it works

The HAVING clause is a very useful tool we can use when querying SQL Server databases.

When I was first learning SQL, I had a tough time understanding the HAVING clause. Looking back, I’m not sure what was so difficult to understand. It’s actually very simple.

Are you struggling to understand the HAVING clause? By the end of this very brief tutorial, I promise you will get it. 

We will discuss just these three topics:

1. What is the HAVING clause?
2. Examples of using the HAVING clause
3. Tips, tricks, and links

First things first,

1. What is the HAVING clause?

The HAVING clause is used in combination with the GROUP BY clause. The HAVING clause basically serves as a WHERE clause to your GROUP BY clause.

The HAVING clause is used to apply a filter to the results of an aggregation.

I should say if you aren’t really sure how the GROUP BY clause works, you should definitely check out my full tutorial on that topic first:

The GROUP BY Clause: A How-To Guide

The best way to understand the HAVING clause is to work through a couple quick examples.

2. Examples of using the HAVING clause

You are the owner of a small business based out of Orlando, Florida. You created a database to track some basic information, such as details about your customers.

Take a look at the data we have in our Customers table:

This table just tracks the customer’s unique customer ID, their first and last name, and the city they live in.

Cool. Let’s think about something we might be interested in gathering from this data. What if we want to know how many customers we have in each city?

I’m sure most of our customers are in our own town of Orlando, but maybe we want to know how many customers are in the other neighboring towns. With the GROUP BY clause, used with the COUNT(*) aggregate, we can easily get that information:

Again folks, if you don’t know anything about the GROUP BY clause, you need to check out my tutorial on the topic:

The GROUP BY Clause: A How-To Guide

So this information is great. Now we know how many shoppers we have from each city. Most of our shoppers are in our own town of Orlando, but it’s cool to see we also have some shoppers in Tampa, Daytona, and Jacksonville.

“Ok, so where does the HAVING clause come in?“

What if we wanted to filter this final result set, and see only cities where we have more than one shopper? We might want to do this if we’re thinking about advertising our business in other cities. It might be a waste of energy to advertise in cities where we only have one shopper.

So we want to restrict our final result set. Normally, this can be accomplished by creating a filter in the WHERE clause. Let’s see if that’s going to work (spoiler alert: It isn’t):

Ok, hmmm, that didn’t work. Maybe we put the WHERE clause in the wrong spot? (spoiler alert: That’s still not the problem):

Here’s that full error message:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Don’t try to interpret that error message.

So how the heck do we create a filter against an aggregate value? Answer: We use the HAVING clause: 

As far as syntax goes, you put your HAVING clause after your GROUP BY clause. Also in the HAVING clause, you outline the aggregate value again, and whatever filters you want to apply to it. In our case, the aggregate is COUNT(*), and the filter we want to apply to it is ‘> 1‘

Here’s another example of a GROUP BY clause using a different aggregate (without a HAVING clause yet):

This query figures out how many of each product we have sold, and the total income earned from each product.

Maybe we want to see our best sellers. In our business, our “best sellers” are products we’ve made more than $500 from.

It’s a simple HAVING clause:

So simple.

3. Tips, Tricks, and links.

Here is a list of a few tips and tricks you should know when using the HAVING clause:

Tip # 1: You can certainly filter on more more than one aggregate function in your HAVING clause

For example, this works fine:

Tip # 2: In your HAVING clause, you are not limited to only applying a filter to your aggregation function

You can apply a filter to some other column if you want to.

For example, let’s go back to this query that doesn’t have a HAVING clause yet:

What if we want to see only information for our Coat Rack and Side Tables products?

We can put that filter in the HAVING clause:

If we wanted, we could still put another filter in the HAVING clause that filters on the aggregate value:

To me, this seems like a bad practice. I think your non-aggregate filters should be in the WHERE clause, and only your aggregate filters should be in the HAVING clause, like this:

It’s just cleaner, in my opinion.

Tip # 3: The HAVING clause won’t work without the GROUP BY clause

The two are besties, you see. The GROUP BY clause can work fine without the HAVING clause, sure, but the HAVING clause won’t work unless the GROUP BY clause is also present!

Links

There is a great book called T-SQL Fundamentals written by Itzik Ben-Gan that goes over several core concepts you should know about SQL Server, including how to use the GROUP BY clause and the HAVING clause. You won’t regret owning this book, trust me. Definitely get it today!

Next Steps:

Leave a comment if you found this tutorial helpful!

If you found this tutorial helpful, you should definitely download the following FREE GUIDE:

FREE 1-page Simple SQL Cheat Sheet on the GROUP BY clause!

As I said earlier, the HAVING clause is basically an extension to the GROUP BY clause. If you need a crash course on the GROUP BY clause, take a look at my full tutorial:

The GROUP BY Clause: A How-To Guide

Also, be sure to check out my other tutorials on other various SQL Server querying tools. One tool you should be familiar with is Set Operators. I have a full tutorial on set operators here:

SQL Server Set Operators: The Ultimate Guide

Set operators are seen very commonly in the real world, which means you should be familiar with them and how they work.

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, please leave a comment. Or better yet, send me an email!