using rollup clause in oracle sql

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. 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.