CUBE

SQL CUBE

CUBE is a Group By extension. You can use WITH CUBE for generating summaries of all possible combinations of Group By columns, as well as a grand total.

EXAMPLE 1 OF CUBE

Example 1:

SELECT dept_name, emp_cnt, SUM(budget) sum_of_budgets
FROM project_dept
WHERE dept_name IN ('Accounting', 'Research')
GROUP BY dept_name, emp_cnt;

dept_name	emp_cnt	sum_of_budgets
Accounting	5	10000.0
Research	5	115000.0
Accounting	6	70000.0
Accounting	10	40000.0
Research	10	70000.0

Example 2:

SELECT dept_name, emp_cnt, SUM(budget) sum_of_budgets
FROM project_dept
WHERE dept_name IN ('Accounting', 'Research')
GROUP BY CUBE (dept_name, emp_cnt);

dept_name	emp_cnt	sum_of_budgets
Accounting	5	10000.0
Accounting	6	70000.0
Accounting	10	40000.0
Accounting	NULL	12000.0
Research	5	115000.0
Research	10	70000.0
Research	NULL	185000.0
NULL	NULL	305000.0
NULL	5	125000.0
NULL	6	70000.0
NULL	10	110000.0

The main difference between the last two examples is that the result set of first example displays only the values in relation to the grouping, while the result set of second example contains, additionally, all possible summary rows. The placeholder for the values in the unneeded columns of summary rows is displayed as NULL. For example, the following row from the result set

NULL

NULL

305000

Shows the sum of all budgets of all existing projects in the table, while the row

NULL

5

125000

Shows the sum of all budgets for all projects that employ exactly five employees.

[Source: MICROSOFT SQL SERVER 2008 A BEGINNER'S GUIDE 4/E By Dušan Petkovic]


Another syntax for the second example above:

SELECT dept_name, emp_cnt, SUM(budget) sum_of_budgets
FROM project_dept
WHERE dept_name IN ('Accounting', 'Research')
GROUP BY dept_name, emp_cnt
WITH CUBE;

EXAMPLE 2 OF CUBE

For example, a simple table Inventory contains the following:

Item	Color	Quantity
Table	Blue	124
Table	Red	223
Chair	Blue	101
Chair	Red	210

SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color
WITH CUBE

Item	Color	QtySum
Chair	Blue	101.00
Chair	Red	210.00
Chair	(null)	311.00
Table	Blue	124.00
Table	Red	223.00
Table	(null)	347.00
(null)	(null)	658.00
(null)	Blue	225.00
(null)	Red	433.00

The following rows from the result set are of special interest:

Chair

(null)

311.00

This row reports a subtotal for all rows having the value Chair in the Item dimension. The value null is returned for the Color dimension to show that aggregate reported by the row includes rows with any value of the Color dimension.

Table

(null)

347.00

This row is similar, but reports the subtotal for all rows having Table in the Item dimension.

(null)

(null)

658.00

This row reports the grand total for the cube. Both the Item and Color dimensions have the value null. This shows that all values of both dimensions are summarized in the row.

(null)	Blue	225.00
(null)	Red	433.00

These two rows report the subtotals for the Color dimension. Both have null in the Item dimension to show that the aggregate data came from rows having any value for the Item dimension.

[Source MSDN]


Another worth reading PDF on SQL CUBE.