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:-| 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.
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:| CUBE | ROLLUP |
| 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. |
