
-- gb_15a.sql
-- 

set autotrace off echo off

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

-- for this to work the mod group mod value
-- must be evenly divisible the by region mod value

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

l

/


