Oracle : Analytic Function包含aggresive function

 原來analytic function可以包含aggresive function

select lgobe,
       name1,
       grouping_id(lgobe, name1) gp,
       grouping_id(lgobe) gp1,
       last_value(sum(dmbtr)) over(partition by lgobe) lv,
       nvl(sum(menge), 0) menge,
       nvl(sum(dmbtr), 0) dmbtr,
       nvl(sum(dmbsm), 0) dmbsm,
       nvl(sum(mengebsm), 0) mengebsm,
       case
         when sum(dmbtr) = 0 then
          '0%'
         else
          to_char(round(sum(dmbsm) / sum(dmbtr) * 100, 0)) || '%'
       end dmbsmp,
       nvl(sum(age000_003), 0) age000_003,
       nvl(sum(age004_014), 0) age004_014,
       nvl(sum(age015_030), 0) age015_030,
       nvl(sum(age031_060), 0) age031_060,
       nvl(sum(age061_090), 0) age061_090,
       nvl(sum(age091_180), 0) age091_180,
       nvl(sum(age181_270), 0) age181_270,
       nvl(sum(age271_360), 0) age271_360,
       nvl(sum(age361_xxx), 0) age361_xxx
  from dwmgr.dm_vw_inv_age_3
 where '1' = '1'
   and rdate = '20110420'
   and mtbez = 'DMO1'
   and werks = 'AZ02'
 group by rollup(lgobe, name1)
 order by gp1 desc, lv desc, gp desc, dmbtr desc