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:

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:

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