RSS Feed

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_nameemp_cntsum_of_budgets
Accounting510000.0
Research5115000.0
Accounting670000.0
Accounting1040000.0
Research1070000.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_nameemp_cntsum_of_budgets
Accounting510000.0
Accounting670000.0
Accounting1040000.0
AccountingNULL12000.0
Research5115000.0
Research1070000.0
ResearchNULL185000.0
NULLNULL305000.0
NULL5125000.0
NULL670000.0
NULL10110000.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

NULLNULL305000

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

NULL5125000

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:

ItemColorQuantity
TableBlue124
TableRed223
ChairBlue101
ChairRed210


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

ItemColorQtySum
ChairBlue101.00
ChairRed210.00
Chair(null)311.00
TableBlue124.00
TableRed223.00
Table(null)347.00
(null)(null)658.00
(null)Blue225.00
(null)Red433.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)Blue225.00
(null)Red433.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.