CUBE Aggregate
Automatically performs all possible aggregations of the specified columns, as an extension to the GROUP BY clause.
You can use the ROLLUP clause with three grouping functions:
Syntax
GROUP BY groupexpression[,…]
Parameters
groupexpression 
One or both of the following:

Restrictions

GROUP BY CUBE does not order data. If you want to sort data, use the ORDER BY Clause. The ORDER BY clause must come after the GROUP BY clause.
 You can use CUBE inside a GROUPING SETS expression, but not inside a ROLLUP expression or another CUBE expression.
Levels of CUBE Aggregation
If n is the number of grouping columns, CUBE creates 2^{n} levels of aggregations. For example:
CUBE (A, B, C) creates all possible groupings, resulting in eight groups:

(A, B, C)

(A, B)

(A, C)

(B, C)

(A)

(B)

(C)

()
If you increase the number of CUBE columns, the number of CUBE groupings increases exponentially. The CUBE query may be resource intensive and produce combinations that are not of interest. In that case, consider using the GROUPING SETS Aggregate , which allows you to choose specific groupings.
Examples
Using CUBE to Return All Groupings
Suppose you have a table that contains information about family expenses for books and electricity:
=> SELECT * FROM expenses ORDER BY Category, Year; Year  Category  Amount ++ 2005  Books  39.98 2007  Books  29.99 2008  Books  29.99 2005  Electricity  109.99 2006  Electricity  109.99 2007  Electricity  229.98
To aggregate the data by both Category and Year using the CUBE aggregate:
=> SELECT Category, Year, SUM(Amount) FROM expenses GROUP BY CUBE(Category, Year) ORDER BY 1, 2, GROUPING_ID(); Category  Year  SUM ++ Books  2005  39.98 Books  2007  29.99 Books  2008  29.99 Books   99.96 Electricity  2005  109.99 Electricity  2006  109.99 Electricity  2007  229.98 Electricity   449.96  2005  149.97  2006  109.99  2007  259.97  2008  29.99   549.92
The results include subtotals for each category and year, and a grand total ($549.92).
Using CUBE with the HAVING Clause
This example shows how you can restrict the GROUP BY results, use the HAVING clause with the CUBE aggregate. This query returns only the category totals and the full total:
=> SELECT Category, Year, SUM(Amount) FROM expenses GROUP BY CUBE(Category,Year) HAVING GROUPING(Year)=1;
Category  Year  SUM
++
Books   99.96
Electricity   449.96
  549.92
The next query returns only the aggregations for the two categories for each year. The GROUPING ID function specifies to omit the grand total ($549.92):
=> SELECT Category, Year, SUM (Amount) FROM expenses
GROUP BY CUBE(Category,Year) HAVING GROUPING_ID(Category,Year)<2 ORDER BY 1, 2, GROUPING_ID(); Category  Year  SUM ++ Books  2005  39.98 Books  2007  29.99 Books  2008  29.99 Books   99.96 Electrical  2005  109.99 Electrical  2006  109.99 Electrical  2007  229.98 Electrical   449.96