zundarz zundarz - 3 months ago 11
SQL Question

How to turn multiple groups into a binary grouping?

I want to report volumes of 'PINK' vs. all other colors

with gumdrops_detail as
(
select 1 as customer_id, 'PINK' as gumdrop_color, 400 as gumdrop_volume from dual union all
select 1 as customer_id, 'PINK' as gumdrop_color, 300 as gumdrop_volume from dual union all
select 1 as customer_id, 'BLUE' as gumdrop_color, 500 as gumdrop_volume from dual union all
select 1 as customer_id, 'BLUE' as gumdrop_color, 600 as gumdrop_volume from dual union all
select 1 as customer_id, 'SNOT' as gumdrop_color, 300 as gumdrop_volume from dual union all
select 1 as customer_id, 'SNOT' as gumdrop_color, 100 as gumdrop_volume from dual union all
select 2 as customer_id, 'PINK' as gumdrop_color, 400 as gumdrop_volume from dual union all
select 2 as customer_id, 'PINK' as gumdrop_color, 300 as gumdrop_volume from dual union all
select 2 as customer_id, 'BLUE' as gumdrop_color, 500 as gumdrop_volume from dual union all
select 2 as customer_id, 'BLUE' as gumdrop_color, 600 as gumdrop_volume from dual union all
select 3 as customer_id, 'SNOT' as gumdrop_color, 300 as gumdrop_volume from dual union all
select 3 as customer_id, 'ECRU' as gumdrop_color, 100 as gumdrop_volume from dual
)
select distinct customer_id,
gumdrop_color,
sum(gumdrop_volume) over (partition by customer_id,
gumdrop_color) as vol_by_color,
sum(case gumdrop_color
when 'PINK' then gumdrop_volume
else gumdrop_volume
end) over (partition by customer_id,
gumdrop_color) as vol_by_pink_non_pink
from gumdrops_detail
order by customer_id


Desired Results



ID COLOR TOTVOL
1 PINK 700
1 NONPINK 1500
2 PINK 700
2 NONPINK 1100
3 PINK 0 --Its OK if this row doesn't return...
3 NONPINK 400

Answer
with gumdrops_detail as
(
 ...
)
select customer_id, B.color,
       sum( decode(B.color,
                   decode(gumdrop_color,'PINK','PINK','NONPINK'), gumdrop_volume, 0))
  from gumdrops_detail,
       (select 'PINK' as color from DUAL union select 'NONPINK' from DUAL) B
 group by customer_id, B.color
 order by customer_id, B.color desc

Result:

1   PINK    700
1   NONPINK 1500
2   PINK    700
2   NONPINK 1100
3   PINK    0
3   NONPINK 400

If ZERO for non existing 'PINK' not required:

with gumdrops_detail as
(
 ...
)
select customer_id, decode(gumdrop_color,'PINK','PINK','NONPINK') color,sum(gumdrop_volume)
  from gumdrops_detail
 group by customer_id, decode(gumdrop_color,'PINK','PINK','NONPINK')
 order by 1,2 desc

P.S.: decode(B.color,decode(gumdrop_color,'PINK','PINK','NONPINK'),gumdrop_volume, 0) can be represented as:

case B.color
when (
      case gumdrop_color when 'PINK' then 'PINK'
                         else 'NONPINK'
      end
     )
then gumdrop_volume
else 0
end