CUBE is an extension to the GROUP BY operator that analyzes data by forming the data into groups in more than one dimension.
CUBE requires an ordered list of grouping expressions (dimensions) as arguments and enables the SELECT statement to calculate subtotals for all possible combinations of the group of dimensions.
SELECT case grouping(state) WHEN 1 THEN 'ALL' ELSE state END AS c_state, case grouping(sex) WHEN 1 THEN 'ALL' ELSE sex end AS c_gender, case grouping(DepartmentId) WHEN 1 THEN 'ALL' ELSE cast(DepartmentId as char(4)) end AS c_dept, COUNT(*), CAST(ROUND(AVG(salary),2) AS NUMERIC(18,2))AS AVERAGE FROM employees WHERE state IN ('MA' , 'CA') GROUP BY CUBE(state, sex, DepartmentId) ORDER BY 1,2,3;
c_state |
c_gender |
c_dept |
COUNT() |
AVERAGE |
---|---|---|---|---|
ALL |
ALL |
200 |
3 |
52,200.00 |
ALL |
ALL |
ALL |
3 |
52,200.00 |
ALL |
F |
200 |
2 |
58,650.00 |
ALL |
F |
ALL |
2 |
58,650.00 |
ALL |
M |
200 |
1 |
39,300.00 |
ALL |
M |
ALL |
1 |
39,300.00 |
CA |
ALL |
200 |
3 |
52,200.00 |
CA |
ALL |
ALL |
3 |
52,200.00 |
CA |
F |
200 |
2 |
58,650.00 |
CA |
F |
ALL |
2 |
58,650.00 |
CA |
M |
200 |
1 |
39,300.00 |
CA |
M |
ALL |
1 |
39,300.00 |
When CUBE calculates a group, CUBE puts a NULL value in the columns whose group is calculated. The distinction is difficult between the type of group each row represents and whether the NULL is a NULL stored in the database or a NULL resulting from CUBE. The GROUPING function solves this problem by returning 1, if the designated column has been merged to a higher level group.
Note that the NULLs generated by CUBE to indicate a subtotal row are replaced with ALL in the subtotal rows, as specified in the query..