ROLLUP Clause in Oracle SQL – PLSQL

In simple terms the ROLLUP clause is used to get the subtotal and grand total in a set of fetched records based on groups.

In other words we can say that ROLLUP clause extends the functionality of the GROUP BY Clause by returning rows containing a subtotal for each group along with a grand total for all groups

Syntax for the ROLLUP clause in Oracle SQL / PLSQL is:
SELECT columns
,aggregate_function ()
FROM table_name
GROUP BY ROLLUP(column(s));

Example:

Using the ROLLUP clause

Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10

If we write our query as:

SELECT employee_name
       ,count(*) Employees
       ,department
FROM employee
GROUP BY ROLLUP(department,employee_name); 

We will get the following result:

Employee_Name Employees Department
Emp B 1 IT
Emp C 1 IT
2 IT
Emp A 1 Sales
Emp E 1 Sales
Emp F 1 Sales
3 Sales
Emp D 1 Support
1 Support
6

Let’s observe the records fetched to understand the ROLLUP clause:

  1. 1st and 2nd record (for Emp B and Emp C) pertain to ‘IT’ department
  2. The 3rd record is the subtotal for the IT department; it states that the total number of employees in ‘IT’ department is 2.
  3. 4th, 5th and 6th records (for Emp A, Emp E amd Emp F) correspond to Sales department.
  4. The 7th record is the subtotal for the ‘Sales’ department and tells us that there are 3 employees in ‘Sales’ department.
  5. 8th record for Emp D corresponds to the “Support’ department
  6. 9th record is the subtotal for the ‘Support’ department and tells us that there is only 1 employee in ‘Support’ department.
  7. Finally the 10th record is the grand total of all the records and tells us that there are total of 6 employees in ‘IT’, ‘Sales’ and ‘Support’ department  combined together.

Tagged , , , , , , , , , , , . Bookmark the permalink.