Group by Oracle: Syntax, Uses, Restrictions

As part of learning the Oracle SQL tutorial, Here is good detail on the group by Oracle

Table of Contents

Group by Oracle

We use Group by in the select statement to group rows into summary rows using column or expression. We often used aggregate or group functions on the group to obtain the summary rows

Syntax

SELECT col1, col2, … col_n,
aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY col1, col2, … col_n
Having group condition;

Oracle server performed the following steps

First, the rows are selected based on where clause
Rows are grouped

The group function is applied to each group
The group that matches the criterion in the having clause are displayed

So the WHERE clause is evaluated first (restricts the query results), then the GROUP BY clause (groups the results of the WHERE), and then the HAVING clause (further restricts the results, by restricting the groups returned).

Some Important points about the group by Oracle

(1) GROUP BY: Breaks down the results of group functions from one large table of data into smaller logical groupings.

(2) WHERE clause cannot restrict a group, so use the HAVING clause.

(3) Do not use the column alias in the GROUP BY clause.

(4) HAVING: restricts the display of groups to those “having” the specified conditions.

(5) The NVL function allows a GROUP BY function to include null values in its calculation.

(6) Any column or expression in the select list that is not an aggregate function must be in the group by clause

(7) The group by column does not have to be in the select list.

Examples of Group Functions in Oracle

Let’s first create the sample tables and then try the group by Oracle

CREATE TABLE "DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
)
CREATE TABLE "EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "DEPT" ("DEPTNO") ENABLE
);
insert into DEPT values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'RESEARCH', 'DELHI');
insert into dept values(40, 'RESEARCH', 'MUMBAI');
insert into emp values( 7698, 'Blake', 'MANAGER', 7839, to_date('1-5-2007','dd-mm-yyyy'), 2850, null, 10 );
insert into emp values( 7782, 'Clark', 'MANAGER', 7839, to_date('9-6-2008','dd-mm-yyyy'), 2450, null, 10 );
insert into emp values( 7788, 'Scott', 'ANALYST', 7566, to_date('9-6-2012','dd-mm-yyyy'), 3000, null, 20 );
insert into emp values( 7789, 'TPM', 'ANALYST', 7566, to_date('9-6-2017','dd-mm-yyyy'), 3000, null, null );
insert into emp values( 7560, 'T1OM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 20 );
insert into emp values( 7790, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, null );
commit;
Select * from emp;

Single Column

Select dept , avg(sal)   from emp  group by dept;

Group by Oracle multiple columns

Select deptno ,job, sum(sal) from emp group by deptno,job

Count function

SELECT dept, COUNT(*) AS "No of employees" FROM emp WHERE sal < 15000
GROUP BY dept;

Min Function

SELECT dept, MIN(sal) AS "Lowest salary" FROM emp 
GROUP BY dept;

Max Function

SELECT dept, MAX(sal) AS "Maximum salary" FROM emp  GROUP BY dept;

Sum Function

SELECT dept, sum(sal) AS "total salary" FROM emp  GROUP BY dept;

order by group by Oracle

SELECT dept, sum(sal) AS "total salary" FROM emp  GROUP BY dept order by dept;

rownum group by oracle

We cannot directly use rownum in the group by but we can use it with the subquery method. Suppose we want to know the top two depts which have highest salary between them, We can use the below query

select  dept , "total_salary"  from (SELECT dept, sum(sal) AS "total_salary" FROM emp  GROUP BY dept order by total_salary desc) where rownum <3;

Hope you like this article

Related Articles
Analytic functions in oracle : Oracle Analytic functions compute an aggregate value based on a group of rows by using over partition by oracle clause , they differ from aggregate functions
rank in oracle : RANK, DENSE_RANK and ROW_NUMBER are oracle analytical function which are used to rank rows in the group of rows called window
Lead function in oracle : Check out LAG function in Oracle & Lead function in Oracle, how to use them in analytic queries and how it works in Oracle sql
Top-N Queries in Oracle : Check out this page on exploring the various ways to achieve Top-N Queries in Oracle like oracle top 100, selecting top 10 rows in oracle
Auto Increment in Oracle : Check out how to use Auto Increment in Oracle before 12c version and with 12 and above version with detailed examples
Aggregate functions in oracle :Group Functions operate on the set of rows and return one row per group. Group functions are avg,min,max,sum,variance
not a single-group group function : Check out a method to resolve ORA-00937: not a single-group group function. This error happens when you are using group functions
not a group by expression: Check out method to resolve ORA-00979: not a group by expression. This error happens when you are using group functions
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj32654.html