T-SQL 101: 88 Filtering groups of data by using HAVING

I’ve previously talked about how the WHERE clause is used to limit the rows that are included in a query. If we’re using a GROUP BY, then WHERE is determining what goes into the grouping. But what if you want to apply a limit that’s based on the outcome of the grouping? That’s what HAVING does.

If I execute the following query:

SELECT Size, 
       COUNT(ProductID) AS NumberOfProducts
FROM dbo.Products 
WHERE IsShownOnPriceList <> 0
GROUP BY Size;

I see this output:

This shows me the number of products for each size of product. A HAVING clause lets me then limit the output to maximums greater than 100. This can’t be done in the WHERE clause. It has to be done after the GROUP BY, and that’s where HAVING is used.

When I execute this query:

SELECT Size, 
       COUNT(ProductID) AS NumberOfProducts
FROM dbo.Products 
WHERE IsShownOnPriceList <> 0
GROUP BY Size
HAVING COUNT(ProductID) > 10;

I see this output:

Only size groups with more than 10 products are returned.

So WHERE filters the rows being grouped, HAVING filters the groups.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2021-03-08