The CUBE in Oracle SQL / PLSQL is an extension for the GROUP BY clause.
Syntax for CUBE in Oracle SQL / PLSQL is:
GROUP BY CUBE column(s)
[ORDER BY column(s)];
Using CUBE in Oracle SQL / PLSQL Query
Suppose we have a table named ‘employee’ as shown below:
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:
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.