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:-
| Apparel | Brand | Quantity |
| Shirt | Gucci | 124 |
| Jeans | Lee | 223 |
| Shirt | Gucci | 101 |
| Jeans | Lee | 210 |
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:
| Apparel | Brand | Quantity |
| Shirt | Gucci | 101.00 |
| Shirt | Lee | 210.00 |
| Shirt | (null) | 311.00 |
| Jeans | Gucci | 124.00 |
| Jeans | Lee | 223.00 |
| Jeans | (null) | 347.00 |
| (null) | (null) | 658.00 |
| (null) | Gucci | 225.00 |
| (null) | Lee | 433.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.
Answer
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.