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:
GROUP BY ROLLUP(column(s));
Using the ROLLUP clause
Suppose we have a table named ‘employee’ as shown below:
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:
Let’s observe the records fetched to understand the ROLLUP clause:
- 1st and 2nd record (for Emp B and Emp C) pertain to ‘IT’ department
- The 3rd record is the subtotal for the IT department; it states that the total number of employees in ‘IT’ department is 2.
- 4th, 5th and 6th records (for Emp A, Emp E amd Emp F) correspond to Sales department.
- The 7th record is the subtotal for the ‘Sales’ department and tells us that there are 3 employees in ‘Sales’ department.
- 8th record for Emp D corresponds to the “Support’ department
- 9th record is the subtotal for the ‘Support’ department and tells us that there is only 1 employee in ‘Support’ department.
- 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.