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 groupingexpression[,...]
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 rightmost 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 rightmost 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