René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Oracle SQL: GROUP BY and ROLLUP

A select <aggregate function>, column_1, column_2, ... column_n group by rollup(column_1, column_2, ... column_n) statement can be written as: 
select aggr_out, column_1, column_2, .... column_n from (
  select
    aggr_func(some_column) aggr_out,
    column_1,
    column_2,
    ...
    column_n
  group by (column_1, column_2, ... column_n)

    union all

  select
    aggr_func(some_column) aggr_out,
    column_1,
    column_2,
    ...
    null
  group by (column_1, column_2, ... null)

    union all

  select
    aggr_func(some_column) aggr_out,
    column_1,
    null,    
    ...
    null
  group by (column_1, null,     ... null)

    union all

  select
    aggr_func(some_column) aggr_out
)
order by column_1, column_2, ..., column_n
This will be demonstrated with the following SQL statements.
First, a table (named test_groupby) is created: 
set feedback off
set pagesize 0

create table test_groupby (
  n number,
  a char(1),
  b char(1),
  c char(1)
);
Some values inserted: 
insert into test_groupby values ( 4 , 'z','t', 'm');
insert into test_groupby values ( 7 , 'z','t', 'm');
insert into test_groupby values ( 5 , 'a','u', 'm');
insert into test_groupby values (11 , 'm','v', 's');
insert into test_groupby values ( 1 , 'r','w', 's');
insert into test_groupby values ( 2 , 'a','u', 'm');
insert into test_groupby values ( 9 , 'a','d', 'm');
insert into test_groupby values ( 8 , 'a','u', 'r');
insert into test_groupby values ( 2 , 'r','w', 'u');
insert into test_groupby values ( 4 , 'z','t', 'm');
insert into test_groupby values ( 1 , 'm','d', 'b');

create view vw_1 as select sum(n) s,      a,      b,      c from test_groupby group by    a,    b,    c;
create view vw_2 as select sum(n) s,      a,      b, null c from test_groupby group by    a,    b, null;
create view vw_3 as select sum(n) s,      a, null b, null c from test_groupby group by    a, null, null;
create view vw_4 as select sum(n) s, null a, null b, null c from test_groupby;

select * from vw_1;

     8 a u r
     9 a d m
     2 r w u
     7 a u m
    15 z t m
     1 m d b
     1 r w s
    11 m v s

select * from vw_2;

     3 r w
    15 a u
    15 z t
    11 m v
     1 m d
     9 a d

select * from vw_3;

    24 a
     3 r
    15 z
    12 m

select * from vw_4;

    54

select s, a, b, c from (
  select * from vw_1 union all
  select * from vw_2 union all
  select * from vw_3 union all
  select * from vw_4
)
order by a, b, c;

    9 a d m
    9 a d
    7 a u m
    8 a u r
   15 a u
   24 a
    1 m d b
    1 m d
   11 m v s
   11 m v
   12 m
    1 r w s
    2 r w u
    3 r w
    3 r
   15 z t m
   15 z t
   15 z
   54

select sum(n), a, b, c from test_groupby group by rollup (a, b, c);

    9 a d m
    9 a d
    7 a u m
    8 a u r
   15 a u
   24 a
    1 m d b
    1 m d
   11 m v s
   11 m v
   12 m
    1 r w s
    2 r w u
    3 r w
    3 r
   15 z t m
   15 z t
   15 z
   54
Cleaning up: 
drop view  vw_4;
drop view  vw_3;
drop view  vw_2;
drop view  vw_1;
drop table test_groupby;

Links

See also grouping and grouping_id.