So, you do not like my first solution? I totally agree it was the quickest thing that came off the press.

It is like life, if you spend a couple of minutes more looking at it, a simpler solution can be found.

How about a filtered roll-up? most of the code is for formatting the results.

--

-- Solve business problem with a roll-up

--

; with cte_rollup as (

select

fweek,

prod_desc,

cast(sum(case when warrantystatus = 'IW' then orders else 0 end) as decimal(9,2)) as iw_total,

cast(sum(case when warrantystatus = 'OOW' then orders else 0 end) as decimal(9,2)) as oow_total,

cast(sum(orders) as decimal(9,2)) as grand_total

from gm_table

group by rollup (fweek, prod_desc)

)

select

fweek, prod_desc, iw_total,

cast((iw_total/ grand_total * 100) as decimal(9,2)) as iw_pct,

oow_total,

cast((oow_total/ grand_total * 100) as decimal(9,2)) as oow_pct

from cte_rollup where prod_desc is not null