
-- gb_15gb.sql
-- original
-- demonstrate grouping sets
-- using columns in reverse order in grouping_id() puts rows in desired order

set autotrace off echo off

col regid format a10
col grpid format a10
col value format 999,9999

-- for this to work the mod values must be multiples

with rowgen as (
   select
      'COUNTRY# ' || to_char(mod(rownum,2)+1,'9') cntryid
      , 'REGION# ' || to_char(mod(rownum,6)+1,'9') regid
      , 'GROUP# ' || to_char(mod(rownum,12)+1,'99') grpid
      , rownum value
   from dual
   connect by level <= 100
)
select cntryid, regid, grpid, sum(value) value
   ,grouping_id(grpid,regid,cntryid) gid
from rowgen
group by grouping sets (
   cntryid,
   rollup(regid,grpid)
)
order by
   grouping_id(grpid,regid,cntryid)
   , regid, grpid

l

/

