CUBE Function

I hope you all familiar with ROLLUP Function now. If not please read before get into CUBE Function.

• The CUBE function can be called as Superset of RollUP function.
• Because It returns the same result set as ROLLUP along with provides some additional information.
• Instead producing a 'hierarchy' of results, CUBE looks at all permutations of the specified dimensions.

Let take an example , Produce a 'CUBE' report showing employee salaries across the dimensions of department and manager.

SELECT DEPT_NO AS DEPTNO
 ,MGR_EMP_NO AS MANAGER
 ,SUM(SALARY_AMOUNT) AS SUMSAL
 FROM EMPLOYEE
 WHERE DEPT_NO < 402
GROUP BY CUBE (DEPT_NO,MGR_EMP_NO)
 ORDER BY 1,2;

DEPTNO	MANAGER	SUMSAL
?	?	591925	(all depts, all managers)
?	801	286750	(all depts, one manager)
?	1003	207725
?	1019	58700
?	1025	38750
100	?	100000	(one dept, all managers)
100	801	100000	(one dept, one manager)
201	?	73450	(one dept, all managers)
201	801	34700
201	1025	38750
301	?	116400
301	801	57700
301	1019	58700
302	?	56500
302	801	56500
401	?	245575
401	801	37850
401	1003	207725

If you observe there are four additional rows (green) are included in the output.
These four rows represent the totals by manager.This report shows all possible permutations of the two specified dimensions.

 Reversing CUBE Parameters:
 What happens when we flip the sequence of the CUBE parameters? Consider the following:

 SELECT DEPT_NO AS DEPTNO
 ,MGR_EMP_NO AS MANAGER
 ,SUM(SALARY_AMOUNT) AS SUMSAL
 FROM EMPLOYEE
 WHERE DEPT_NO < 402
GROUP BY CUBE (MGR_EMP_NO,DEPT_NO)
 ORDER BY 1,2;

Yes, The output is exactly  returns same.There is no difference in output becacuse CUBE function looks at all permutations of the specified parameters, order of the arguments doesn't matter - unlike ROLLUP where it does matter.

 we can use the GROUPING and COALESCE functions to make the report more readable.

 SELECT CASE GROUPING (department_number)
 WHEN 1 THEN 'All Depts'
 ELSE (COALESCE (department_number,'Null Dept') ) END AS Deptno
 ,CASE GROUPING (manager_employee_number)
 WHEN 1 THEN 'All Managers'
 ELSE manager_employee_number END AS Mngr
 ,SUM(salary_amount) AS SumSal
 FROM employee
 WHERE department_number < 402
 GROUP BY CUBE (manager_employee_number,department_number)
 ORDER BY 1,2;
 

Deptno	Mngr	SumSal
100	801	100000
100	All Managers	100000
201	801	34700
201	1025	38750
201	All Managers	73450
301	801	57700
301	1019	58700
301	All Managers	116400
302	801	56500
302	All Managers	56500
401	801	37850
401	1003	207725
401	All Managers	245575
All Depts	801	286750
All Depts	1003	207725
All Depts	1019	58700
All Depts	1025	38750
All Depts	All Managers	591925

As more dimensions are added to the cube, more permutations and thus more rows will result.

 If n is the number of dimensions in a cube, then 2**n represents the number of permutations generated:
 2 dimensions - 4 permutations
 3 dimensions - 8 permutations
 4 dimensions - 16 permutations
 etc.
Example

Produce a 'cube' report showing employee salaries across the three dimensions of department, manager and job code for department numbers less than 302.


SELECT CASE GROUPING (department_number)
 WHEN 1 THEN 'All Depts'
 ELSE (COALESCE (department_number,'Null Dept') ) END AS Deptno

,CASE GROUPING (manager_employee_number)
 WHEN 1 THEN 'All Managers'
 ELSE manager_employee_number END AS Mngr

 ,CASE GROUPING (job_code)
 WHEN 1 THEN 'All Job Codes'
 ELSE (COALESCE (job_code,'Null Job Code') ) END AS Job

,SUM(salary_amount) AS SumSal
 FROM employee
 WHERE department_number < 302
GROUP BY CUBE (manager_employee_number,department_number, job_code)
 ORDER BY 1,2;

Deptno	Mngr	Job	SumSal
100	801	111100	100000
100	801	All Job Codes	100000
100	All Managers	All Job Codes	100000
100	All Managers	111100	100000
201	801	211100	34700
201	801	All Job Codes	34700
201	1025	All Job Codes	38750
201	1025	222101	38750
201	All Managers	211100	34700
201	All Managers	All Job Codes	73450
201	All Managers	222101	38750
301	801	All Job Codes	57700
301	801	311100	57700
301	1019	All Job Codes	58700
301	1019	312101	29450
301	1019	312102	29250
301	All Managers	312102	29250
301	All Managers	311100	57700
301	All Managers	All Job Codes	116400
301	All Managers	312101	29450
All Depts	801	All Job Codes	192400
All Depts	801	111100	100000
All Depts	801	211100	34700
All Depts	801	311100	57700
All Depts	1019	312101	29450
All Depts	1019	All Job Codes	58700
All Depts	1019	312102	29250
All Depts	1025	All Job Codes	38750
All Depts	1025	222101	38750
All Depts	All Managers	All Job Codes	289850
All Depts	All Managers	311100	57700
All Depts	All Managers	211100	34700
All Depts	All Managers	111100	100000
All Depts	All Managers	312101	29450
All Depts	All Managers	312102	29250
All Depts	All Managers	222101	38750
All Depts	All Managers	312102	29250
All Depts	All Managers	222101	38750

To help interpret the output, use the following color-coding scheme:

(department,manager, job_code)       Each unique combo of dept/mgr/job
(department, manager)                        Each unique combo of dept/mgr
(department, job_code)                        Each unique combo of dept/job
(manager, job_code)                             Each unique combo of mgr/job
(department)                                         All depts
(manager)                                             All mgrs
(job_code)                                             All jobs
( )                                                           Grand Total