Cube in oracle sql

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:

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