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

grouping and grouping_id [Oracle SQL]

grouping(expression)

grouping_id(expression)

grouping_id(expression-1, expression-2)

grouping_id(expression-1, ... , expression-n)
create table grouping_ex (
  a number,
  b char(1),
  c char(1)
);
insert into grouping_ex values (1, 'a', '*');
insert into grouping_ex values (2, 'b', '*');
insert into grouping_ex values (3, 'a', '*');
insert into grouping_ex values (4, 'a', '*');
insert into grouping_ex values (5, 'b', '#');
insert into grouping_ex values (6, 'a', '#');
insert into grouping_ex values (7, 'a', '#');
insert into grouping_ex values (8, 'b', '#');
insert into grouping_ex values (9, 'a', '#');
select sum_a,
       b,
       c,
       gr_b,
       gr_c,
       gr_id_b,
       gr_id_c,
       gr_id_bc,
       power(2,1) * gr_id_b +
       power(2,0) * gr_id_c gr_id_bc_ 
from (
  select          sum(a)            sum_a,
                   b                b,
                   c                c,
                   grouping   (b)   gr_b,
                   grouping   (c)   gr_c,
                   grouping_id(b)   gr_id_b,
                   grouping_id(c)   gr_id_c,
                   grouping_id(b,c) gr_id_bc
  from             grouping_ex
  group by rollup  (b,c)
);
     SUM_A B C       GR_B       GR_C    GR_ID_B    GR_ID_C   GR_ID_BC  GR_ID_BC_
---------- - - ---------- ---------- ---------- ---------- ---------- ----------
        22 a #          0          0          0          0          0          0
         8 a *          0          0          0          0          0          0
        30 a            0          1          0          1          1          1
        13 b #          0          0          0          0          0          0
         2 b *          0          0          0          0          0          0
        15 b            0          1          0          1          1          1
        45              1          1          1          1          3          3