sql - Percentage of group total -

i'm trying add new column calculate percentage total of each pack type week.

the code below results in following.

so new column display percentage of total number of hectoliters specific week.

code:

set datefirst 1  select   datepart (wk, t0.u_orc_be_proddate) [week produced], --display week number of date produced ( case      when t5.u_orc_be_name '%cans%'          'cans'     when t5.u_orc_be_name '%bottles%'         'bottles'     when t5.u_orc_be_name '%key keg%'         'key keg'     when t5.u_orc_be_name '%ss keg%'         'ss keg'     when t5.u_orc_be_name '%e-keg%'         'ss keg' end )as [pack type], --collate item types pack types sum(t5.u_orc_be_hectoliter * t0.cmpltqty) [total hectoliters] --calculate total hl in order   owor t0   inner join oitt t1 on t0.itemcode = t1.code  inner join oitm t2 on t1.code = t2.itemcode left join [@orc_be_pack_type] t5 on t5.code = t2.u_orc_be_pack_type left join [@orc_be_style_h] t6 on t6.code= t2.u_orc_be_shortcode        (t5.u_orc_be_hectoliter * t0.cmpltqty) != 0  , u_orc_be_processtype = 'packaging' ,      (      t0.[u_orc_be_proddate] >=  dateadd(wk, datediff(wk, 0, getdate()) - 13, 0)      , t0.[u_orc_be_proddate] < dateadd(wk, datediff(wk, 0, getdate()), 0)     )  --for previous 12 weeks  group datepart(wk, t0.u_orc_be_proddate),        t0.u_operator,     (     case          when t5.u_orc_be_name '%cans%'              'cans'         when t5.u_orc_be_name '%bottles%'             'bottles'         when t5.u_orc_be_name '%key keg%'             'key keg'         when t5.u_orc_be_name '%ss keg%'             'ss keg'         when t5.u_orc_be_name '%e-keg%'             'ss keg'     end     )  order datepart (wk, t0.u_orc_be_proddate) asc 

use sum() over() aggregate full amount each calendar unit , percentage calculation that.

 select       d.*     , ([total hectoliters] * 100.0) / sum([total hectoliters]) on (partition [week produced]) [weekly pct] (       select             datepart(wk, t0.u_orc_be_proddate)        [week produced]             --display week number of date produced           , (             case                   when t5.u_orc_be_name '%cans%' 'cans'                   when t5.u_orc_be_name '%bottles%' 'bottles'                   when t5.u_orc_be_name '%key keg%' 'key keg'                   when t5.u_orc_be_name '%ss keg%' 'ss keg'                   when t5.u_orc_be_name '%e-keg%' 'ss keg'             end             )                                         [pack type]             --collate item types pack types           , sum(t5.u_orc_be_hectoliter * t0.cmpltqty) [total hectoliters]       --calculate total hl in order       owor t0             inner join oitt t1 on t0.itemcode = t1.code             inner join oitm t2 on t1.code = t2.itemcode             inner join [@orc_be_pack_type] t5 on t5.code = t2.u_orc_be_pack_type             left join [@orc_be_style_h] t6 on t6.code = t2.u_orc_be_shortcode       (t5.u_orc_be_hectoliter * t0.cmpltqty) <> 0             , u_orc_be_processtype = 'packaging'             --for previous 12 weeks             , (t0.[u_orc_be_proddate] >= dateadd(wk, datediff(wk, 0, getdate()) - 13, 0)             , t0.[u_orc_be_proddate] < dateadd(wk, datediff(wk, 0, getdate()), 0)             )       group             datepart(wk, t0.u_orc_be_proddate)           , t0.u_operator           , (             case                   when t5.u_orc_be_name '%cans%' 'cans'                   when t5.u_orc_be_name '%bottles%' 'bottles'                   when t5.u_orc_be_name '%key keg%' 'key keg'                   when t5.u_orc_be_name '%ss keg%' 'ss keg'                   when t5.u_orc_be_name '%e-keg%' 'ss keg'             end             ) ) d order       [week produced] asc 

by way because clause insists alias t5 meets conditions there no point in using left join on t5. is, because every row has meet conditions null result t5 ignored, therefore outer join irrelevant table.