Introduction to SQL HAVING Clause
‘Having’ clause in SQL is used for aggregation operations along with ‘Where’, ‘group by’ & ‘order by’ condition statements. It is applied on a table/ database where there is a need for filtering aggregate results and allows ‘group by’ and ‘order by’ conditions. When the ‘having’ clause is used in a query, it is expected that the resulting output data set can have more than one record from the table/ database.
Why HAVING and not WHERE?
We see that HAVING and WHERE clauses perform a very similar task to filter out the results. Then what was the need for the HAVING clause? Why couldn’t the WHERE clause be used with aggregate functions?
To answer this, we would need to understand how the SQL engine treats the two clauses. The FROM clause in every SQL command tells the engine from where to read the rows. The data is stored on the disk and is fetched into the memory for processing. As the rows are read one by one from the disk to the memory, they are checked for the WHERE clause. The rows that fail the WHERE clause aren’t loaded into the memory. Thus, the WHERE clause is evaluated for each row as the SQL engine processes them.
On the contrary, the HAVING clause comes into the picture only after the rows have been loaded into the memory. Once loaded into the memory, the aggregate functions perform their task on the rows HAVING the desired condition.
Now, if we were to put a WHERE clause with the aggregate function such as avg(), this would confuse the SQL engine on whether to include the row for calculating the average or not. Essentially, we would be commanding the engine to not read the row since it did not pass the avg() criteria in the WHERE clause. But hey, to determine whether it passed or failed the avg() calculation criteria, the row needs to be read into the memory. A state of deadlock.
Syntax:
SELECT <column(s)>
FROM <table>
WHERE <condition> -- optional
GROUP BY <column(s)> -- groups the rows to apply aggregate function
HAVING <condition> -- aggregate function in the condition
ORDER BY <column(s)>; -- define the sorting order, optional
How HAVING Clause Works?
The GROUP BY clause always accompanies the HAVING clause. The GROUP BY clause groups together the data that match a certain criterion. It has three phases – split, apply, and combine. The split-phase divides the rows into groups. The apply phase applies some aggregate functions on the groups of data. The combined phase produces a single result by combining the groups with the aggregate function result.
Now that the groups are formed, the HAVING clause comes into the picture. The HAVING clause then filters out the groups which do not satisfy the given condition.
Code:
SELECT Col_A, avg(Col_B) as Col_B
FROM MyTable
GROUP BY Col_A
HAVING avg(Col_B)>30
Thus, in the example above, we see that the table is first to split into three groups based on the column Col_A. The aggregate function to calculate the average of Col_B values is then applied to the groups. This results in a single row for each group. The rows are then combined and filtered based on the condition in the HAVING clause.
Example
Now let us look at a real-world example.
Consider we have the following table of customers and the orders that they have placed with us.
| CustomerID | CustomerName | City | Country |
| 1 | Anja Damian | Berlin | Germany |
| 2 | Denny Cockett | México D.F. | Mexico |
| 3 | Eleanor Calnan | México D.F. | Mexico |
| 4 | Albertha Albury | London | UK |
| 5 | Latisha Nembhard | Luleå | Sweden |
| 6 | Madalene Bing | Mannheim | Germany |
| 7 | Rebecka Beegle | Strasbourg | France |
| 8 | Rosy Tippie | Madrid | Spain |
| 9 | Audie Khan | Marseille | France |
| 10 | Hildegard Burrowes | Tsawassen | Canada |
| 11 | Cordell Dutremble | London | UK |
| 12 | Nora Reyna | Buenos Aires | Argentina |
| 13 | Ursula Laforest | México D.F. | Mexico |
| 14 | Claudie Neel | Bern | Switzerland |
| 15 | Portia Yee | São Paulo | Brazil |
| 16 | Angila Segarra | London | UK |
| 17 | Lise Wexler | Aachen | Germany |
| 18 | Ned Mendivil | Nantes | France |
| 19 | Sara Vidaurri | London | UK |
| 20 | Tayna Navin | Graz | Austria |
| 21 | Pura Ray | São Paulo | Brazil |
| 22 | Erika Byard | Madrid | Spain |
| 23 | Jimmie Luke | Lille | France |
| 24 | Shayla Byington | Bräcke | Sweden |
| 25 | Christiana Boden | München | Germany |
| 26 | Irina Nitta | Nantes | France |
| 27 | Bryanna Alls | Torino | Italy |
| 28 | Norah Picken | Lisboa | Portugal |
| 29 | Moriah Stwart | Barcelona | Spain |
| 30 | Idella Harriott | Sevilla | Spain |
| OrderID | CustomerID | OrderDate |
| 10254 | 14 | 11-07-1996 |
| 10258 | 20 | 17-07-1996 |
| 10259 | 13 | 18-07-1996 |
| 10263 | 20 | 23-07-1996 |
| 10264 | 24 | 24-07-1996 |
| 10265 | 7 | 25-07-1996 |
| 10267 | 25 | 29-07-1996 |
| 10278 | 5 | 12-08-1996 |
| 10280 | 5 | 14-08-1996 |
| 10289 | 11 | 26-08-1996 |
| 10290 | 15 | 27-08-1996 |
| 10297 | 7 | 04-09-1996 |
| 10303 | 30 | 11-09-1996 |
| 10308 | 2 | 18-09-1996 |
| 10311 | 18 | 20-09-1996 |
| 10326 | 8 | 10-10-1996 |
| 10327 | 24 | 11-10-1996 |
| 10328 | 28 | 14-10-1996 |
| 10331 | 9 | 16-10-1996 |
| 10337 | 25 | 24-10-1996 |
| 10340 | 9 | 29-10-1996 |
| 10342 | 25 | 30-10-1996 |
| 10347 | 21 | 06-11-1996 |
| 10351 | 20 | 11-11-1996 |
| 10352 | 28 | 12-11-1996 |
| 10355 | 4 | 15-11-1996 |
| 10360 | 7 | 22-11-1996 |
| 10362 | 9 | 25-11-1996 |
| 10363 | 17 | 26-11-1996 |
| 10364 | 19 | 26-11-1996 |
| 10365 | 3 | 27-11-1996 |
| 10366 | 29 | 28-11-1996 |
| 10368 | 20 | 29-11-1996 |
| 10370 | 14 | 03-12-1996 |
| 10378 | 24 | 10-12-1996 |
| 10382 | 20 | 13-12-1996 |
| 10383 | 4 | 16-12-1996 |
| 10384 | 5 | 16-12-1996 |
| 10386 | 21 | 18-12-1996 |
| 10389 | 10 | 20-12-1996 |
| 10390 | 20 | 23-12-1996 |
| 10391 | 17 | 23-12-1996 |
| 10396 | 25 | 27-12-1996 |
| 10400 | 19 | 01-01-1997 |
| 10402 | 20 | 02-01-1997 |
| 10403 | 20 | 03-01-1997 |
| 10408 | 23 | 08-01-1997 |
| 10410 | 10 | 10-01-1997 |
| 10411 | 10 | 10-01-1997 |
| 10414 | 21 | 14-01-1997 |
| 10422 | 27 | 22-01-1997 |
| 10426 | 29 | 27-01-1997 |
| 10430 | 20 | 30-01-1997 |
| 10431 | 10 | 30-01-1997 |
| 10434 | 24 | 03-02-1997 |
| 10435 | 16 | 04-02-1997 |
| 10436 | 7 | 05-02-1997 |
| 10442 | 20 | 11-02-1997 |
Now, we want to know customers of which countries have placed a combined total of 5 or more orders with us. It could be a single customer placing more than 5 orders or 5 customers placing 1 order each.
To accomplish this, we would need to:
Step 1: Join the two tables.
Step 2: Group the customers based on their countries.
Step 3: Count the number of orders for each group.
Step 4: Filter the results for 5 or more orders.
Let’s formulate the command:
Code:
SELECT C.Country, COUNT(O.OrderId) as NumberOfOrders -- Step 1,3
FROM Customers C -- Step 1
INNER JOIN Orders O on C.CustomerID = O.CustomerID -- Step 1
GROUP BY C.Country -- Step 2
HAVING COUNT(O.OrderId) >= 5 -- Step 4
ORDER BY COUNT(O.OrderId) DESC
Here are the results:
| Country | NumberOfOrders |
| Austria | 10 |
| France | 9 |
| Sweden | 7 |
| Germany | 6 |
| UK | 6 |
Conclusion – SQL HAVING Clause
Thus, we have seen what the purpose of the HAVING clause is and how does it work. It is important to understand the basic working, or else you may end up getting confused about why the HAVING clause is not producing the desired results. Keep playing with various tables and joins and combinations along with the HAVING clause.
Recommended Articles
This is a guide to SQL HAVING Clause. Here we discuss working of the HAVING clause in SQL and the example with the following table of customers. You can also go through our other suggested articles –
