ROLLUP and CUBE Clauses in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

ROLLUP and CUBE Clauses in Oracle with Examples

In this article, I am going to discuss ROLLUP and CUBE Clauses in Oracle with Examples. Please read our previous article where we discussed the HAVING Clause in Oracle with Examples.

ROLLUP and CUBE Clauses in Oracle:

1. Special Clauses in Oracle.
2. Used for finding subtotal and grand total based on columns.
3. Working along with the “GROUP BY” clause.
4. ROLLUP will find sub and grand total based on a single column.
5. CUBE will find sub and grand totals based on multiple columns.

Example to understand ROLLUP and CUBE Clauses in Oracle:

We are going to use the following Employee table to understand the ROLLUP and CUBE Clauses in Oracle with Examples.

Please execute the below SQL query to drop the existing Employee table if any and create a new Employee table with the required data.

DROP Table Employee;

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(15),
  Department VARCHAR(10),
  Salary NUMBER(8, 2),
  Gender VARCHAR(10),
  Comm INT,
  City VARCHAR(10)
);

INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1001, 'John', 'IT', 35000, 'Male', 3500, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1002, 'Smith', 'HR', 45000, 'Male', 4500, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1003, 'James', 'Finance', 50000, 'Male', 5000, 'Delhi');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1004, 'Mike', 'Finance', 50000, 'Male', NULL, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1005, 'Linda', 'HR', 75000, 'Female', NULL, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1006, 'Anurag', 'IT', 35000, 'Male', NULL, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1007, 'Priyanla', 'HR', 45000, 'Female', NULL, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1008, 'Sambit', 'IT', 55000, 'Female', 5500, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1009, 'Pranaya', 'IT', 57000, 'Female', 5700, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1010, 'Hina', 'HR', 75000, 'Male', 7500, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1011, 'Warner', 'Finance', 55000, 'Female', NULL, 'London');

ROLLUP Syntax in Oracle:

Following is the syntax to use the ROLLUP clause in Oracle.

GROUP BY ROLLUP(<COL1>, <COL2>, <COL3>,……..,<COL n>)

Example: ROLLUP with a single column:

SELECT Department, COUNT(*) FROM Employee GROUP BY ROLLUP(Department);

When you execute the above query, you will get the following output. As you can see, it is grouping the records based on the Department column and showing the department name and the number of employees belongs to that department. Apart from that, it is also showing the grand total i.e. total number of employees (adding the total number of employees in each department).

Example. ROLLUP with multiple columns

SELECT Department, Gender, COUNT(*) FROM Employee GROUP BY ROLLUP(Department, Gender);

When you execute the above query, you will get the following output. First, it groups the employees by Department and then by Male and shows the total number of employees in Department by Gender and then sum employees by department and showing the total number of employees in each department and finally shows total employees in each department.

Note: In the above example ROLLUP is finding sub and grand total based on a single column (Department). If we want to find sub and grand total then use the “CUBE” clause.

Syntax to use CUBE clause in Oracle:

Following is the syntax to use the ROLLUP clause in Oracle.

GROUP BY CUBE(<COL1>, <COL2>,…………………,<COL n>)

Example: CUBE with a single column:

SELECT Department, COUNT(*) FROM Employee GROUP BY CUBE(Department) ORDER BY Department;

Output:

Example: CUBE with multiple columns

SELECT Department, Gender, COUNT(*) FROM Employee GROUP BY CUBE(Department, Gender) ORDER BY Department;

Output:

GROUPING_ID() Function in Oracle:

The GROUPING_ID() Function in Oracle is used in a more compact way to identify sub and grand total rows.
ID NUMBER 1: To represent subtotal of first grouping column.
                     2: To represent subtotal of second grouping column.
                     3: Grand total.

Syntax to use GROUPING_ID()in Oracle:

Following is the syntax to use GROUPING_ID() Function in Oracle.

GROUPING_ID(<COL1>,<COL2>,…..)

Example:

SELECT Department, Gender, COUNT(*), GROUPING_ID(Department, Gender)
FROM Employee
GROUP BY CUBE(Department, Gender)
ORDER BY Department;

Output:

In the next article, I am going to discuss FETCH FIRST Clause in Oracle with Examples. Here, in this article, I try to explain ROLLUP and CUBE in Oracle with Examples and I hope you enjoy this ROLLUP and CUBE in Oracle with Examples article.