EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • 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 Oracle Tutorial Oracle GROUP BY HAVING

Oracle GROUP BY HAVING

Priya Pedamkar
Article byPriya Pedamkar

Updated June 20, 2023

Oracle GROUP BY HAVING

Introduction to Oracle GROUP BY HAVING

In the Oracle database, the GROUP BY clause is used as a clause in conjunction with a SELECT statement. It allows us to group or cluster identical data in a table based on specified columns. The GROUP BY clause rearranges the data into distinct groups or clusters.

ADVERTISEMENT
Popular Course in this category
ORACLE DBA Database Management System Course Bundle - 2 Courses in 1

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax

Let us now look at the syntax of the GROUP BY HAVING clause, and it is very simple.

SELECT
expression_1, expression_2, expression_n, aggregate_function(expression)
FROM
table_name
GROUP BY
expression_1, expression_2, expression_n
HAVING
condition;

Parameters

expression_1, expression_2, expression_n:  This refers to the expressions which must be used with GROUP BY function.

aggregate_function(expression): It refers to an aggregate function like SUM, COUNT,  MAX, MIN

table_name: It refers to the name of the table on which GROUP BY function is applied.

condition: It refers to the condition which we want to apply on the result set from the GROUP BY clause to restrict or filter the rows returned by the GROUP BY.

How does GROUP BY HAVING Work in Oracle?

In the previous section, we discussed the definition and syntax of the GROUP BY HAVING in Oracle. In this section, we are going to discuss how it works. GROUP BY and HAVING are two different clauses. Let us now discuss how the query executes when we use both of them in the same query. The GROUP BY clause, when the statement or query executes, segregates or groups each table row into a particular group. Each group consists of similar data.

The data is grouped in groups based on some expression which is mentioned after the group by clause. This returns a result set that consists of many groups. If the condition stated after the HAVING clause is true, the result set includes the group. One important point to note is that the HAVING clause filters groups, whereas the WHERE clause filters rows.

Examples to Implement Oracle GROUP BY HAVING

In this section, we will discuss a few examples of various cases in which the Oracle clause with having is used.

Example #1

GROUP BY WITHOUT USING HAVING: In this case, we will see an example to understand how to use a simple GROUP BY clause without using HAVING. In this example, we will use the GROUP BY clause on a column city present in the employee table. This should group the rows based on the different cities. Let us prepare a query for the same.

Code:

SELECT avg(SALARY), city from employee group by city;

Output: 

column city

Explanation: In the above query, we find the average salaries by grouping the data based on cities in the employee table. As we can see in the screenshot above, the output shows the average salaries for each city.

Example #2

GROUP BY USING HAVING: We will use the HAVING clause with GROUP BY and check how it works. In this example, we will use the GROUP BY clause with a HAVING condition on the column city present in the employee table. This should group the rows based on the different cities, and then the final result set should contain only those rows of groups that satisfy the HAVING condition. Let us prepare a query for the same.

Code:

SELECT avg(SALARY), city from employee group by city having avg(SALARY) >55000;

Output: 

HAVING condition

Explanation: In the above query, we can see that the rows in the table will be first grouped by city, and then only those groups whose average salary is greater than 55000 shall be displayed in the output.

Example #3

GROUP BY USING MORE THAN ONE HAVING CONDITION: We will use more than one HAVING condition with the GROUP BY clause and check how it works. We can also call this scenario HAVING with complex conditions. In this example, we will use the GROUP BY clause on the column city present in the employee table with more than one HAVING condition. This should group the rows based on the different cities, and then the final result set should contain only those rows of groups that satisfy both the HAVING condition. Let us prepare a query for the same.

Code:

select avg(SALARY), SUM(vehicles)no_of_vhicles from employee group by city having avg(SALARY) >35000 AND SUM(vehicles)>2 ORDER BY AVG(SALARY);

Output: 

Oracle GROUP BY HAVING3

Explanation: In the above query, we can see that the rows in the table will be first grouped by city. First, we filter the initial result set to include only groups with an average salary greater than 35000. Only those groups which satisfy both these conditions are the final result. The final result is then ordered in ascending order by average salary and displayed as output. As we can see in the screenshot, the output displays the result in ascending order for groups that have an average salary greater than 35000 and vehicles greater than two.

Conclusion

In this article, we discussed the definition of GROUP BY with HAVING in the Oracle database, the syntax, and its working in detail. To get a practical perspective, we discussed examples for each scenario to have a better understanding of the topic.

Recommended Articles

This is a guide to Oracle GROUP BY HAVING. Here we discuss an introduction to Oracle GROUP BY HAVING with appropriate syntax, how it works, and query examples. You can also go through our other related articles to learn more –

  1. Oracle COMMIT 
  2. Oracle UNIQUE Constraint
  3. UNION in Oracle
  4. Natural Join in Oracle
ADVERTISEMENT
GOLANG Course Bundle - 6 Courses in 1
23+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
iOS DEVELOPER Course Bundle - 61 Courses in 1
147+ Hours of HD Videos
61 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
JAVA SERVLET Course Bundle - 18 Courses in 1 | 6 Mock Tests
56+ Hours of HD Videos
18 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
RED HAT LINUX Course Bundle - 5 Courses in 1
28+ Hours of HD Videos
5 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & 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

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

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW