The ROLLUP operator can be used in a the GROUP BY clause of a SELECT statement to group the selected rows based on the values of expressions, thus returning a single row summary for each group. Thus, a SELECT statement using the ROLLUP operator returns both 'regular rows' and 'super-aggregate rows'. Super-aggregate rows are rows that are derived by applying the same set of function(s) that were used to obtain the grouped rows. In other words, super-aggregate rows contain sub-total values.
The CUBE operator can be used to produce cross-tabulation values, thus produces totals in all possible dimensions. Thus CUBE produces both the line item totals (which can be produced by ROLLUP) in addition to column totals.
A NULL value in a super-aggregate row represents 'all values'. The GROUPING() function can be used to distinguish between a singular row with a null value in a column and a null value that represents the set of all values. The expression in GROUPING() must match one of the expressions in the GROUP BY clause. GROUPING() returns 1 only if the value of expr in the row is a null representing the set of all values, else a 0 (zero) is returned.
ROLLUP and CUBE operators can both be used in CREATE MATERIALIZED VIEW.
The examples are based on the following table and data:
SQL> desc test
Name Null? Type
---------------------------------------
YEAR NUMBER(4)
REGION CHAR(7)
DEPT CHAR(2)
PROFIT NUMBER
SQL> select * from test;
YEAR REGION DE PROFIT
---------- ------- -- ----------
1995 West A1 100
1995 West A2 100
1996 West A1 100
1996 West A2 100
1995 Central A1 100
1995 East A1 100
1995 East A2 100
7 rows selected.
Example 1: ROLLUP
SQL> select year, region, sum(profit), count(*)
2 from test
3 group by rollup(year, region);
YEAR REGION SUM(PROFIT) COUNT(*)
---------- ------- ----------- ----------
1995 Central 100 1
1995 East 200 2
1995 West 200 2
1995 500 5
1996 West 200 2
1996 200 2
700 7
7 rows selected.
A summary of the above information in tabular form:
Year Central(A1+A2) East(A1+A2) West(A1+A2)
1995 (100+NULL) (100+100) (100+100) 500
1996 (NULL+NULL) (NULL+NULL) (100+100) 200
700
Example 2: ROLLUP and GROUPING()
SQL> select year, region, sum(profit),
2 grouping(year) "Y", grouping(region) "R"
3 from test
4 group by rollup (year, region);
YEAR REGION SUM(PROFIT) Y R
---------- ------- ----------- ---------- ----------
1995 Central 100 0 0
1995 East 200 0 0
1995 West 200 0 0
1995 500 0 1
1996 West 200 0 0
1996 200 0 1
700 1 1
7 rows selected.
Note, GROUPING(expr) function returns 1 for super-aggregate rows if the null value for expr represents the set of all values.
Example 3: CUBE
SQL> select year, region, sum(profit), count(*)
2 from test
3 group by cube(year, region);
YEAR REGION SUM(PROFIT) COUNT(*)
---------- ------- ----------- ----------
1995 Central 100 1
1995 East 200 2
1995 West 200 2
1995 500 5
1996 West 200 2
1996 200 2
Central 100 1
East 200 2
West 400 4
700 7
Thus, in addition to the subtotals produced by ROLLUP, the CUBE operator
completes all possible entries in a crosstab report.
A summary of the above information in tabular form:
Year Central(A1+A2) East(A1+A2) West(A1+A2)
1995 (100+NULL) (100+100) (100+100) 500
1996 (NULL+NULL) (NULL+NULL) (100+100) 200
100 200 400 700
Example 4: CUBE and GROUPING()
SQL> select year, region, sum(profit),
2 grouping(year) "Y", grouping(region) "R"
3 from test
4 group by cube (year, region);
YEAR REGION SUM(PROFIT) Y R
---------- ------- ----------- ---------- ----------
1995 Central 100 0 0
1995 East 200 0 0
1995 West 200 0 0
1995 500 0 1
1996 West 200 0 0
1996 200 0 1
Central 100 1 0
East 200 1 0
West 400 1 0
700 1 1
10 rows selected.
Note, GROUPING(expr) function returns 1 for super-aggregate rows if the null
value for expr represents the set of all values.
Important:
The GROUP BY clause must contain all SELECT expressions that are not group function arguments or an ORA-979 (not a GROUP BY expression) will results.
GROUPING(expr) will result in an ORA-909 (invalid number of arguments) if greater than one expression specified.
Source : Oracle Metalink
