nola94 nola94 - 3 years ago 69
SQL Question

Total Sum and Partial Sum

I am currently using SSMS. I am pulling data, and trying to get two different columns that sum prices. The two columns 'ChangeSpend' and 'TotalSpend' both reference the same column and this is where I am running into problems.

I want ChangeSpend to return the sum of all the codes per receipt that start with V.Ch% (so they exclude all the others) and the TotalSpend to sum all of the codes for each receipt.

Here is my current code:

SELECT
Receipt
,ReceiptCode
,ReceiptAmount
,sum(ReceiptAmount) over (Partition by Receipt) as TotalSpend
,(CASE WHEN ReceiptCode = 'V.Ch%' then sum(ReceiptAmount)
over (Partition by Receipt)
ELSE 0
END) as ChangeSpend
FROM tableA
LEFT OUTER JOIN tableB
on A.Receipt = B.Receipt
WHERE ReceiptCode LIKE 'V.%'
ORDER BY Receipt


However, my query currently 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 I would like it to 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 for any help

Answer Source

Try

,SUM(CASE WHEN ReceiptCode LIKE 'V.Ch%' THEN ReceiptAmount ELSE 0 END) 
 OVER (Partition by Receipt) 
   AS ChangeSpend
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download