sql - Total Sum and Partial Sum -

i using ssms. pulling data, , trying 2 different columns sum prices. 2 columns 'changespend' , 'totalspend' both reference same column , running problems.

i want changespend return sum of codes per receipt start v.ch% (so exclude others) , totalspend sum of codes each receipt.

here current code:

select        receipt       ,receiptcode       ,receiptamount       ,sum(receiptamount) on (partition receipt) totalspend       ,(case when receiptcode = 'v.ch%' sum(receiptamount)                                               on (partition receipt)        else 0       end) changespend   tablea   left outer join tableb   on a.receipt = b.receipt   receiptcode 'v.%'   order receipt 

however, query prints this:

receipt    receiptcode    receiptamount    totalspend    changespend 1          v.cha          5                20            0          1          v.rt           2                20            0 1          v.chb          6                20            0 1          v.abc          7                20            0 2          v.cha          20               21            0 2          v.abc          1                21            0 3          v.cha          4                14            0 3          v.chb          1                14            0 3          v.tye          7                14            0 3          v.chs          2                14            0 

and print this:

receipt    receiptcode    receiptamount    totalspend    changespend 1          v.cha          5                20            11          1          v.rt           2                20            11 1          v.chb          6                20            11 1          v.abc          7                20            11 2          v.cha          20               21            20 2          v.abc          1                21            20 3          v.cha          4                14            7 3          v.chb          1                14            7 3          v.tye          7                14            7 3          v.chs          2                14            7 

thanks help

try

,sum(case when receiptcode 'v.ch%' receiptamount else 0 end)   on (partition receipt)     changespend