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:

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:

• 1^st, 3^rd and 5^th row tells us the commission and the total salary grouped by IT, Sales and Support department along with the respective commissions
• 2^nd, 4^th and 6^th row tells us the total salary in the IT, Sales and Support department neglecting the commissions being given to the employees.
• 7^th, 8^th and 9^th 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 10^th row tells us the total salary being given to the employees, neglecting the department and the commissions of the employees.