ROLLUP Aggregate
Automatically performs subtotal aggregations as an extension to the GROUP BY clause. ROLLUP
performs these aggregations across multiple dimensions, at different levels, within a single SQL query.
You can use the ROLLUP
clause with three grouping functions:
Syntax
ROLLUP grouping-expression[,...]
Parameters
group‑expression |
One or both of the following:
|
Restrictions
GROUP BY ROLLUP
does not sort results. To sort data, an ORDER BY
clause must follow the GROUP BY
clause.
Levels of Aggregation
If n is the number of grouping columns, ROLLUP
creates n+1 levels of subtotals and grand total. Because ROLLUP
removes the right-most column at each step, specify column order carefully.
Suppose that ROLLUP(A, B, C)
creates four groups:
(A, B, C)
(A, B)
(A)
()
Because ROLLUP
removes the right-most column at each step, there are no groups for (A, C)
and (B, C)
.
If you enclose two or more columns in parentheses, GROUP BY
treats them as a single entity. For example:
ROLLUP(A, B, C)
creates four groups:(A, B, C) (A, B) (A) ()
ROLLUP((A, B), C)
treats(A, B)
as a single entity and creates three groups:(A, B, C) (A, B) ()
Example: Aggregating the Full Data Set
The following example shows how to use the GROUP BY
clause to determine family expenses for electricity and books over several years. The SUM
aggregate function computes the total amount of money spent in each category per year.
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
For the expenses
table, ROLLUP
computes the subtotals in each category between 2005–2007:
- Books: $99.96
- Electricity: $449.96
- Grand total: $549.92.
Use the ORDER BY
clause to sort the results:
=> SELECT Category, Year, SUM(Amount) FROM expenses GROUP BY ROLLUP(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 | | 549.92
Example: Using ROLLUP with the HAVING Clause
This example shows how to use the HAVING clause with ROLLUP
to restrict the GROUP BY
results. The following query produces only those ROLLUP
categories where year
is subtotaled, based on the expression in the GROUPING
function:
=> SELECT Category, Year, SUM(Amount) FROM expenses GROUP BY ROLLUP(Category,Year) HAVING GROUPING(Year)=1 ORDER BY 1, 2, GROUPING_ID(); Category | Year | SUM -------------+------+-------- Books | | 99.96 Electricity | | 449.96 | | 549.92
The next example rolls up on (Category
, Year
), but not on the full results. The GROUPING_ID
function specifies to aggregate less than three levels:
=> SELECT Category, Year, SUM(Amount) FROM expenses GROUP BY ROLLUP(Category,Year) HAVING GROUPING_ID(Category,Year)<3 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