• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » Group by Oracle: Syntax, Uses, Restrictions

Group by Oracle: Syntax, Uses, Restrictions

September 11, 2022 by techgoeasy Leave a Comment

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

Table of Contents

  • Group by Oracle
  • Some Important points about the group by oracle
  • Examples of Group Functions in Oracle
    • Single Column
    • Group by oracle multiple columns
    • Count function
    • Min Function
    • Max Function
    • Sum Function
    • order by group by oracle
    • rownum 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 groups 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 sql

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;
group by oracle

Single Column

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

Group by oracle multiple columns

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

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 group by but we can use it with subquery method. Suppose we want to know the top two dept which has highest salary between them, We can use 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 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

Filed Under: Oracle, Oracle Sql

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to generate sql monitor report in Oracle
  • How to find the long running (longops session) in Oracle
  • How to find concurrent request id from Sid or spid
  • how to check recycle bin in oracle: Purge, enable/disable
  • how to check fra size in oracle: Location, usage, change size

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us