What is the difference between rollup and cube?

The ROLLUP clause extends GROUP BY to return a row containing a subtotal for each group of rows, plus a row containing a total for all the groups, to make it simple, it calculates multiple levels of subtotals of a group of columns.

The CUBE clause extends GROUP BY to return rows containing a subtotal for all combinations of columns, plus a row containing the grand total, to make it simple, the CUBE operator is used in the GROUP BY clause of a SELECT statement to return a result set of multidimensional (multiple columns) nature.

Example:

Apparel  Brand  Quantity

Shirt         Gucci  124

Jeans       Lee      223

Shirt          Gucci  101

Jeans       Lee      210

CUBE:

SELECT Apparel, Brand, SUM(Quantity) AS QtySum

FROM product

GROUP BY CUBE (Apparel, Brand)

The query above will return:

Apparel  Brand  Quantity

Shirt         Gucci    225

NULL       Gucci  225

Jeans       Lee      433

NULL        Lee    433

NULLNULL658

JeansNULL433

ShirtNULL225

ROLLUP:

SELECT Apparel, Brand, SUM(Quantity) AS QtySum

FROM product

GROUP BY ROLLUP (Apparel, Brand)

The query above will return:

Apparel  Brand  Quantity

Jeans      Lee      433

Jeans      NULL  433

Shirt        Gucci    225

Shirt        NULL  225

NULL      NULL  658