As part of learning the Oracle SQL tutorial, Here is good detail on the group by Oracle
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
