Cube

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.

Calculating Average Salaries

The following query uses data from the Employees table to calculate the average salary by state, gender, and department.

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