SQL HAVING

The SQL HAVING keyword provides a search condition for a group or aggregate. The SQL HAVING clause works together with the SQL SELECT clause. The SQL HAVING clause is somewhat similar to the SQL WHERE clause, because it specifies a search condition.

There is one important difference between SQL HAVING and SQL WHERE clauses. The SQL WHERE clause condition is tested against each and every row of data, while the SQL HAVING clause condition is tested against the groups and/or aggregates specified in the SQL GROUP BY clause and/or the SQL SELECT column list.

It is important to understand that if a SQL statement contains both SQL WHERE and SQL HAVING clauses the SQL WHERE clause is applied first, and the SQL HAVING clause is applied later to the groups and/or aggregates.

We will demonstrate how to use SQL HAVING using our Weather table:

CityAverageTemperatureDate
New York22 C10/10/2005
Seattle21 C10/10/2005
Washington20 C10/10/2005
New York18 C10/09/2005
Seattle20 C10/09/2005
Washington17 C10/09/2005

Our goal is to select all cities, with average temperature greater than 19 C. To accomplish that we are going to use the following SQL HAVING statement:

SELECT City, AVG(AverageTemperature) FROM Weather GROUP BY City HAVING AVG(AverageTemperature) > 19

The result of the above SQL HAVING statement is displayed below:

CityAverageTemperature
Seattle20.5 C
New York20 C

In the SQL GROUP BY chapter we used almost the same SQL statement but without the HAVING part:

SELECT City, AVG(AverageTemperature) FROM Weather GROUP BY City

The result of it was the following:

CityAverageTemperature
Washington18.5 C
Seattle20.5 C
New York20 C

By applying the SQL HAVING clause to this result set consisting of three groups, we remove the Washington group simply because its AverageTemperature value is less than 19.