Introduction to Oracle GROUP BY HAVING
GROUP BY in oracle database can be defined as a clause which when used with SELECT statement can be used to arrange the identical data in a table to groups or clusters and HAVING clause can be defined as a filter clause which when used with GROUP BY can be used to filter the groups retrieved or returned by the GROUP BY clause, it follows the WHERE clause in a SELECT statement but it precedes the ORDER BY clause.
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. Only those groups for which the condition evaluates to true are included and the rest is not included.
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 row of the table 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 which consists of many groups. The HAVING clause now filters or checks each group from the result set returned by the GROUP BY clause based on the condition mentioned after the HAVING clause. If the condition evaluates to be true it will be included in the group or else it is not included. 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 are going to discuss a few examples for various cases in which the Oracle clause with having is used.
4.5 (7,304 ratings)
View Course
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:
Explanation: In the above query we are finding 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: In this case, we are going to 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:
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. As we can see in the screenshot that only the groups with salaries greater than 55000 are displayed.
Example #3
GROUP BY USING MORE THAN ONE HAVING CONDITION: In this case, we are going to use more than one HAVING condition with the GROUP BY clause and check how it works. We can also call this scenario as 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:
Explanation: In the above query we can see that the rows in the table will be first grouped by city and then initial result set is filtered so that only groups whose average salary is greater than 35000 and then again it is checked that groups which satisfy the first condition must also satisfy that the sum of vehicles present in the group must be greater than two. Only those groups which satisfy both these conditions is 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 it’s 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 does it works, and query examples. You can also go through our other related articles to learn more –
