SQL HAVING Clause and GROUP BY

The SQL Having clause is used to filter summary results from a GROUP BY.  It specifies the search condition for the group or aggregate.

SQL HAVING is only used with SELECT.  It is mostly used when a GROUP BY is present, if one isn’t there is an implicit single aggregated group.

When constructing a SELECT statement using HAVING the order is:

SELECT columnlist
FROM table
WHERE condition
GROUP BY columns
HAVING condition
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:

SELECT yearID, teamID, SUM(ab) AS [At Bats], SUM(hr) AS [Home Runs] FROM Batting GROUP BY yearid, teamid ORDER BY yearid, teamid;
-- 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. 

SQL HAVING - Summarize Home Runs for Year

1961 Roger Maris Tiger Field home run

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!

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;
-- 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:

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;
-- 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 100000?

/* Try it now… if you get stuck you can click “toggle answer.” */
/*  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 have questions please let me know.

Kris Wenzel
 

Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA.He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame.Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

Click Here to Leave a Comment Below 0 comments