GROUP BY in Oracle

Group By clause in Oracle groups the result data according to the columns mentioned in it.

The values of grouping columns differ for all rows.

NULL values are grouped into one row without any separate representation of each of them.

Typically, group by clause in Oracle is used with an aggregate expression.

ROLLUP in the group by computes multiple levels of grouping at once.

Learn Oracle Step by Step

GROUP BY CLAUSE

Syntax

  SELECT columns | expressions
    FROM table_name
   WHERE conditions
GROUP BY {columns | ROLLUP (columns)};
Parameters Explanation

columns

  • Single or multiple columns to be grouped

expressions

  • Expressions to be grouped

table_name

  • Single or multiple tables from which data is grouped

Example

  • Grouping data using columns of the table
  SELECT dept_no, COUNT(*)
    FROM employees
GROUP BY dept_no;

Here, the number of employees in each department is derived.

  • Grouping data using ROLLUP syntax
  SELECT dept_no as Dept, supervisor_no as Supervisor, 
         COUNT(*) as Count_Emp
    FROM employees
GROUP BY ROLLUP (dept_no, supervisor_no);

In this example, query fetches data grouped by supervisor_no first followed by data grouped by dept_no. The last row displays the total number of rows table has.

DEPT

SUPERVISOR

COUNT_EMP

10

1

10

7782

1

10

7839

1

10

3

20

7566

2

20

7788

1

20

7839

1

20

7902

1

20

5

30

7698

5

30

7839

1

30

6

14

Please feel free to write us at connect@oraclemine.com or hioraclemine@gmail.com if you find mistakes or errors in any of the topics. Your suggestions to improve the course will help others.

You can also Subscribe here to stay updated on the latest posts of OracleMine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.