SQL Having Clause

SQL Tutorial Home

This lesson covers the SQL HAVING clause, which is a complementary clause to the GROUP BY clause. The HAVING clause acts as a filter at the group level. If you think of WHERE as a filter clause oriented to records, then HAVING is a filter clause oriented to groups of records created with the GROUP BY clause.

In the HAVING clause, you can put logical conditions involving columns and aggregate functions. All the valid elements in the SELECT list, can be involved in the HAVING clause.

Here is the example table. Suppose you work on a farm growing apples. The database contains a single table named apple with all the production history. The table has the following schema:

Year Apple Variety Number of Trees in Production Tons Produced Harvest day Price per Ton First summer storm
2020 Red Delicious 2000 102 06/23/2020 54.50 07/03/2020
2020 Magic Green 700 33 06/12/2020 62.60 07/03/2020
2020 Red Globus 500 26 05/30/2020 71.50 07/03/2020
2019 Red Delicious 1800 87 07/15/2019 52.25 07/12/2019
2019 Magic Green 500 26 06/28/2019 59.40 07/12/2019
2019 Red Globus 500 27 05/28/2019 68.00 07/12/2019
2018 Red Delicious 1800 92 07/02/2018 56.75 06/03/2018
2018 Red Globus 500 24 05/30/2018 66. 06/03/2018
2017 Red Delicious 1500 76.5 07/18/2017 51.45 07/30/2017
2016 Red Delicious 1500 72 06/26/2016 47.60 06/23/2016

Suppose you want to create a report showing those years where the farm produced more than 100 tons of apples. You need a condition similar to:

SUM(tons_produced) > 100

You cannot put this condition in the WHERE clause, because the WHERE does not allow an aggregation function. This is where you use the HAVING clause:


SELECT year,
       SUM(tons_produced) AS Total_apple_produced
FROM   apple
GROUP  BY year
HAVING SUM ( tons_produced ) > 100

The result:

Year Total_apple_produced
2020 161
2019 140
2018 116

In this example query you obtain the pairs <years , apple_variety> where the dollar per tree metric is higher than 3.0. Review the lesson Using the SQL GROUP BY Clause in a SELECT Query for the dollar per tree metric details. The query is:


SELECT apple_variety, 
       AVG((tons_produced * price_per_ton)/number_of_trees) AS avg_dollar_per_tree,
FROM   apple
GROUP  BY apple_variety
HAVING AVG((tons_produced * price_per_ton)/number_of_trees) > 3.0

Here is the result:

apple_variety avg_dollar_per_tree
Magic Green 3.01
Red Globus 3.51

Closing Words

In this lesson you used the SQL HAVING clause, with examples of the HAVING clause using aggregate functions. The next lesson introduces SUBQUERIES, a really interesting and powerful topic. The subqueries can be used in several different parts of a SQL SELECT and even in other SQL statements like UPDATE, DELETE or INSERT. Keep going, learn SQL and increase your skills!

IN THIS PAGE