Updated June 20, 2023
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.
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:
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:
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:
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 –
