SQL server - Difference between CUBE operator & ROLLUP operator.

Difference between CUBE operator and ROLLUP operator

CUBE operator is used in the GROUP BY clause of a SELECT statement to return a result set of multidimensional (multiple columns) nature.

Example:
A table product has the following records:-
ApparelBrandQuantity
ShirtGucci124
JeansLee223
ShirtGucci101
JeansLee210

CUBE can be used to return a result set that contains the Quantity subtotal for all possible combinations of Apparel and Brand:
SELECT Apparel, Brand, SUM(Quantity) AS QtySum
FROM product
GROUP BY Apparel, Brand WITH CUBE
The query above will return:
ApparelBrandQuantity
ShirtGucci101.00
ShirtLee210.00
Shirt(null)311.00
JeansGucci124.00
JeansLee223.00
Jeans(null)347.00
(null)(null)658.00
(null)Gucci225.00
(null)Lee433.00

ROLLUP:- Calculates multiple levels of subtotals of a group of columns.

Example:
SELECT Apparel,Brand,sum(Quantity) FROM Product GROUP BY ROLLUP (Apparel,Brand);
The query above will return a sum of all quantities of the different brands.

List out the difference between CUBE operator and ROLLUP operator.

CUBE generates a result set that represents aggregates for all combinations of values in the selected columns.

ROLLUP generates a result set that represents aggregates for a hierarchy of values in the selected columns.

Difference between CUBE operator and ROLLUP operator

Difference between CUBE and ROLLUP:
CUBEROLLUP
It’s an additional switch to GROUP BY clause. It can be applied to all aggregation functions to return cross tabular result sets.It’s an extension to GROUP BY clause. It’s used to extract statistical and summarized information from result sets. It creates groupings and then applies aggregation functions on them.
Produces all possible combinations of subtotals specified in GROUP BY clause and a Grand Total.Produces only some possible subtotal combinations.
What are the guidelines to use bulk copy utility of SQL Server?
SQL server bulk copy utility - While importing data, the destination table must already exist,While exporting to a file, bcp will create the file.......
SQL server capabilities - What are the capabilities of Cursors?
Capabilities of Cursors - Cursors can support various functionalities that are listed here.......
What are the ways to controlling Cursor Behavior?
SQL server Cursor Behavior - Cursors behavior can be controlled by dividing them into cursor types: forward-only, static, keyset-driven, .......
Post your comment
Discussion Board
Result set is Wrong for Cube Operator
The result set u displayed to demonstrate the Cube operator is Wrong
Venkata Prasad 06-8-2016