EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQL HAVING Clause
 

SQL HAVING Clause

Priya Pedamkar
Article byPriya Pedamkar

Updated March 17, 2023

SQL HAVING Clause

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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
Note: GROUP BY clause is required with the HAVING clause. This is because the Having clause needs a group of data to apply an aggregate function and filter out the results.

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.

How HAVING clause works

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

We hope that this EDUCBA information on “SQL HAVING Clause” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL Insert Query
  2. Foreign Key in SQL
  3. Distinct Keyword in SQL
  4. SQL Views

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW