Oracle ROLLUP and CUBE usage

CUBE and ROLLUP are new SQL keywords since Oracle8i. 9i and-11G are not new. It is an extension of the GROUP BY clause, allows you to calculate the subtotal of different levels in the SELECT statement.

CUBE is another extension of the group by clause. It allows you to calculate all possible combinations of subtotal to generate various summary reports.

The following describes their usage

Select B, c, D from Atest group by rollup (B, C, D );

Equivalent

Select B, c, D from Atest group by B, C, D;

Union all

Select B, c, null from Atest group by B, C

Union all

Select B, null, null from Atest group by B

Select B, c from Atest group by cube (B, C );

Merge the group by subsets of all (B, C) Combinations

Equivalent

Select B, c from Atest group by B, C

Union all

Select B, null from Atest group by B

Union all

Select null, c from Atest group by c

Union all

Select null, null from Atest group by null