SQL – Use HAVING to filter GROUP BY

WHERE filters individual rows. HAVING filters aggregated rows.

When you use GROUP BY, you are grouping rows together into aggregated rows.

How do you filter the aggregated rows? You use the HAVING clause.

Example of using HAVING

Let’s say we want to see all NFL divisions where all teams have played in the Super Bowl at least once.

Query

SELECT Conference, Division, 
MIN(SuperBowlWins + SuperBowlLosses) as [LeastNumberOfSuperBowlAppearances]
FROM [NFLTeams]
GROUP BY Conference, Division
HAVING MIN(SuperBowlWins + SuperBowlLosses) > 0
ORDER BY Conference, Division

Results

ConferenceDivisionLeastNumberOfSuperBowlAppearances
AFCEast1
AFCWest1
NFCEast3
NFCSouth1
NFCWest1

How does this work?

GROUP BY Conference, Division

There are 32 teams in the NFL. We are grouping them based on their Conference and Division and creating an aggregated row for each group. We end up with the following:

ConferenceDivisionLeastNumberOfSuperBowlAppearances
AFCEast1
AFCNorth0
AFCSouth0
AFCWest1
NFCEast3
NFCNorth0
NFCSouth1
NFCWest1

MIN(SuperBowlWins + SuperBowlLosses)

SuperBowlWins + SuperBowlLosses = number of Super Bowl appearances.

MIN() gives us the smallest number within a group.

Therefore this is giving us the least number of Super Bowl appearances within each division.

HAVING MIN(SuperBowlWins + SuperBowlLosses) > 0

HAVING filters out groups not meeting the filter condition. We only want to see divisions where all teams have appeared in the Super Bowl at least once.

There are three divisions with teams that have never been to the Super Bowl, hence we end up with the following results:

ConferenceDivisionLeastNumberOfSuperBowlAppearances
AFCEast1
AFCWest1
NFCEast3
NFCSouth1
NFCWest1

For reference – the NFLTeams table

ConferenceDivisionTeamSuper Bowl WinsSuper Bowl Losses
AFCEastBuffalo Bills04
AFCEastMiami Dolphins23
AFCEastNew England Patriots65
AFCEastNew York Jets10
AFCNorthPittsburgh Steelers62
AFCNorthBaltimore Ravens20
AFCNorthCincinnati Bengals02
AFCNorthCleveland Browns00
AFCSouthHouston Texans00
AFCSouthIndianapolis Colts22
AFCSouthJacksonville Jaguars00
AFCSouthTennessee Titans01
AFCWestKansas City Chiefs21
AFCWestLas Vegas Raiders32
AFCWestLos Angeles Chargers01
AFCWestDenver Broncos35
NFCEastDallas Cowboys53
NFCEastWashington Redskins32
NFCEastNew York Giants41
NFCEastPhiladelphia Eagles12
NFCNorthChicago Bears11
NFCNorthDetroit Lions00
NFCNorthGreen Bay Packers41
NFCNorthMinnesota Vikings04
NFCSouthCarolina Panthers02
NFCSouthAtlanta Falcons02
NFCSouthNew Orleans Saints10
NFCSouthTampa Bay Buccaneers10
NFCWestSan Francisco 49ers52
NFCWestSeattle Seahawks12
NFCWestArizona Cardinals01
NFCWestLos Angeles Rams13

Leave a Comment