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 group-expression[,…]
Parameters
group-expression |
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 2n 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