CUBE in Oracle SQL – PLSQL

The CUBE in Oracle SQL / PLSQL is an extension for the GROUP BY clause.

Syntax for CUBE in Oracle SQL / PLSQL is:
SELECT column(s),
AGGREGATE_FUNCTION(s),
FROM table_name
GROUP BY CUBE column(s)
[ORDER BY column(s)];

Example:

Using CUBE in Oracle SQL / PLSQL Query
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 department
       ,commission
       ,SUM(salary) Total_Salary
FROM employee
GROUP BY CUBE (department, commission);

We will get the following output:

Department Commission Total_Salary
IT 20 48000
IT 48000
Sales 10 82000
Sales 82000
Support 5 30000
Support 30000
5 30000
10 82000
20 48000
160000

Let’s understand the fetched results to understand CUBE:

  • 1st, 3rd and 5th row tells us the commission and the total salary grouped by IT, Sales and Support department along with the respective commissions
  • 2nd, 4th and 6th row tells us the total salary in the IT, Sales and Support department neglecting the commissions being given to the employees.
  • 7th, 8th and 9th row tells us the total salary being given to the employees having commissions of 5 , 10 and 20 respectively neglecting the departments in which they work.
  • The 10th row tells us the total salary being given to the employees, neglecting the department and the commissions of the employees.

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