Use SQL HAVING to filter summary results from a GROUP BY. It specifies the search condition for the group or aggregate.
When constructing a SELECT statement using HAVING the order is:
GROUP BY columns
ORDER by column list.
So what do we mean that SQL HAVING filters summary results? Let’s look at an example:
SQL HAVING Example
Consider the following query which returns the per year pro baseball team batting statistics:
-- Answer SELECT yearID , teamID , SUM(ab) AS [At Bats] , SUM(hr) AS [Home Runs] FROM Batting GROUP BY yearid , teamid ORDER BY yearid , teamid;
How do we filter the results? If we use WHERE, we’ll end up filtering rows before they’re summarized.
Keep in mind it is easy to get WHERE and HAVING mixed up. They are not the same and serve completely different purposes!
To filer the summarized rows we’ll use SQL HAVING. Let’s only show batting stats for teams 250 or more home runs within a season!
-- Answer SELECT yearID, teamID, SUM(ab) AS [At Bats], SUM(hr) AS [Home Runs] FROM Batting GROUP BY yearid, teamid HAVING Sum(hr) > 250 ORDER BY yearid, teamid;
HAVING Clause Expressions
You can also do calculation with the aggregate function with the SQL HAVING. In this example we’re returning all batting seasons where 4.5% of the at bats results in home runs:
-- Answer SELECT yearID, teamID, SUM(ab) AS [At Bats], SUM(hr) AS [Home Runs] FROM Batting GROUP BY yearid, teamid HAVING (CAST(Sum(hr) as float) / CAST(Sum(ab) as float)) >= 0.045 ORDER BY yearid, teamid;
Notice I had to use CAST to convert the data type.
Now why don’t you try one. Can you write a query in the space below to count the number of “At Bats” and “Home Runs” by year? Only include years where the “At Bats” were greater than 100,000?
/* Answer */ SELECT yearID, SUM(ab) AS [At Bats], SUM(hr) AS [Home Runs] FROM Batting GROUP BY yearid HAVING SUM(ab) > 100000 ORDER BY yearid
If you’re looking to learn more about using the HAVING clause, then I would recommend these resources:
- Summarize your Results with the GROUP BY clause
- What is the Difference between Where and Having Clauses?
- Looking for a super SQL book? Check out SQL Queries for Mere Mortals.