Introduction to GROUP BY in Oracle
The GROUP BY Oracle clause is used to decide the rows in a table into groups. It is used in the SELECT statement for grouping the rows by values of column or expression. The GROUP BY clause groups the results by one or more columns value.
Points of Concentration
- If the group function is included in a SELECT statement then individual result column(s) cannot be used without GROUP BY clause.
- The extra non-group functional columns should be declared in the GROUP BY clause.
- Rows can be pre excluded before dividing them into groups by using the WHERE clause.
- Column ALIAS cannot be used in the GROUP BY clause.
- By default, rows are sorted by ascending order of the column(s) included in the GROUP BY list.
- The columns applied upon the GROUP BY clause need not be part of the SELECT list.
- If the group function is included with the non-group functional column in a SELECT statement then GROUP BY clause must be used.
Syntax
1. Syntax without Group Function
SELECT Column_1, Column_2,..., Column_N FROM Table_Name WHERE condition(s)
GROUP BY Column_Name(s) ORDER BY Column(S);
2. Syntax with Group Function
SELECT Column_1, Column_2,..., GROUP_FUN(Column) FROM Table_Name
WHERE condition(s) GROUP BY Column_Name(s) ORDER BY Column(S);
Description:
- Col_1/2/n: The column(s) or calculation as per your requirement.
- Table_Name: As per your requirement
- WHERE: It’s optional, depends on your requirement
- GROUP_FUN: AVG, SUM, MIN, MAX
Example: Without GROUP Function
SQL> SELECT Job FROM Emp GROUP BY Job;
Output:
Example: With GROUP Function
SQL> SELECT Deptno, AVG (Sal) FROM Emp GROUP BY Deptno;
Output:
Explanation: The above two examples (without and with ALIAS name) clearly shows that how GROUP BY clause groups the rows based on condition.
Implementations of Oracle GROUP BY Clause with Examples
In this section, we’ll see the implementation of the Oracle GROUP BY Clause and its behavior. For that, we will use the below sample table (Emp) with 14 records to understand the Oracle GROUP BY Clause behavior.
SQL> SELECT * from Emp;
Output:
Example #1
GROUP BY clause with Non-GROUP Functional columns
SQL > SELECT Empno, Ename, Job, Deptno FROM Emp GROUP BY Deptno;
Output:
The above SELECT statement throws an error because there are four Non GROUP Functional columns included in the SELECT statement but only one column is included in the GROUP BY clause.
To prevent this error all non-group functional columns should be included in the GROUP BY clause. Example below:
SQL > SELECT Empno, Ename, Job, Deptno FROM Emp GROUP BY Empno, Ename,Job, Deptno;
Output:
Example #2
Column ALIAS name in GROUP BY Clause
SQL> SELECT TO_CHAR (Hiredate, 'YYYY') Year FROM Emp GROUP BY Year;
14 Online Courses | 8 Hands-on Projects| 120+ Hours| Verifiable Certificate of Completion
4.5
View Course
Output:
The above SELECT statement throwing an invalid identifier error because column ALIAS name (Year) being used in GROUP BY Clause. As per rule Column ALIAS cannot be used in the GROUP BY clause. The correct SELECT statement is below:
SQL> SELECT TO_CHAR (Hiredate, 'YYYY') Year FROM Emp GROUP BY
TO_CHAR (Hiredate, 'YYYY');
Output:
Example #3
GROUP BY Clause with GROUP Function
SQL> SELECT Deptno, AVG (Sal) FROM Emp GROUP BY Deptno;
Output:
In the above example, Output showing an Average salary of each Deptno after grouping the Deptno.
SQL> SELECT Deptno, Job, SUM (Sal) FROM Emp GROUP BY Deptno;
Output:
But the above SELECT statement throwing an error because there are two Non GROUP Functional columns included with GROUP functional column in the SELECT statement but only one Non-GROUP Functional column included in the GROUP BY clause.
To prevent this error all non-group functional columns should be declared in the GROUP BY clause. Example below:
SQL> SELECT Deptno, Job, SUM (Sal) FROM Emp GROUP BY Deptno, Job;
Output:
Example #4
GROUP FUNCTIONAL column and Non-GROUP Functional column without GROUP BY Clause
SQL> SELECT Deptno, AVG (Sal) FROM Emp;
Output:
The above SELECT statement pop-ups an error because GROUP FUNCTIONAL column is being used with the Non-GROUP Functional column. As per rule Non-GROUP, the Functional column must be included in the GROUP BY clause if the GROUP FUNCTIONAL column is being used.
See below the correct SELECT statement for the above scenario:
SQL> SELECT Deptno, AVG (Sal) FROM Emp GROUP BY Deptno;
Output:
Example #5
Few more examples for Group-wise Summaries
SQL> SELECT Deptno, AVG (Sal) FROM Emp GROUP BY Deptno ORDER BY
AVG (Sal);
Output:
SQL> SELECT Deptno, MIN (Sal), MAX (Sal) FROM Emp GROUP BY deptno;
Output:
SQL> SELECT Deptno, MIN (Sal), MAX (Sal) FROM Emp WHERE Job=’CLERK’
GROUP BY deptno;
Output:
TIPS:
- Do not need to use the GROUP BY clause if only the GROUP FUNCTIONAL column is being used.
- GROUP FUNCTIONAL column ALIAS name can be used in the ORDER BY clause but not in the GROUP BY clause.
- GROUP FUNCTIONAL column always returns a single row result.
Conclusion
Oracle GROUP BY Clause is an expression or keyword which groups a result set into subsets that have matching values for one or more columns. To get the result set in groups or need to apply aggregate or GROUP function with the Non-GROUP Functional column, Oracle GROUP BY Clause is a good option.
Recommended Articles
This is a guide to GROUP BY in Oracle. Here we discuss the introduction, points of concentration and implementations of Oracle GROUP BY Clause with examples. You can also go through our other related articles to learn more–
