SQL Server 2008 How do I sum rows that match in criteria

Milla 0
Viewed 21

This is what my table looks like:

RefNum Year CorrespVNum Proceeds BaseCost
12345 2019 54321 12345.69 10000.00
12345 2019 54321 500.69 6000.00
12345 2019 65432 12345.69 10000.00
12345 2019 65432 500.69 6000.00
23456 2020 33344 50000.00 15000.00
34567 2021 11155 521.00 1000.00
34567 2021 11155 17.00 800.00
34567 2021 11155 85.00 100.00

I want the result to look like this:

RefNum Year CorrespVNum TotalProceeds TotalBaseCost
12345 2019 54321 12846.38 16000.00
12345 2019 65432 12846.38 16000.00
23456 2020 33344 50000.00 15000.00
34567 2021 11155 623.00 1900.00

So the matching criteria are the Refnum, Year and CorrespVnum. I have been messing around with a CTE query and I can't seem to get it to work. It works if I only have 2 matching rows but if there are more than 2 then it doesn't work.

Does anyone have any idea how to do this?

sql-server-2008 aggregate sql-server sql
2 Answers

you can try this, keep it simple:

with table_1 (Refnum,Year,CorrespVNum,Proceeds,BaseCost)
as
(
          Select '12345', '2019', '54321',12345.69,10000 
Union all Select '12345', '2019', '54321',500.69,6000 
Union all Select '12345', '2019', '65432',12345.69,10000
Union all Select '12345', '2019', '65432',500.69,6000 
Union all Select '34567', '2021', '33344',500000,15000 
Union all Select '34567', '2021', '11155',521,1000 
Union all Select '34567', '2021', '11155',17,800 
Union all Select '34567', '2021', '11155',85,100 
)
Select 
    RefNum,
    Year,
    CorrespVNum,
    Sum(BaseCost) as TotalCaseCost
from 
    table_1
group by 
    RefNum,
    Year,
    CorrespVNum
user199506270 
select   RefNum
        ,Year
        ,CorrespVNum
        ,sum(Proceeds) as TotalProceeds 
        ,sum(BaseCost) as TotalBaseCost
from     t
group by RefNum, Year, CorrespVNum
RefNum Year CorrespVNum TotalProceeds TotalBaseCost
12345 2019 54321 12845 16000
12345 2019 65432 12845 16000
23456 2020 33344 50000 15000
34567 2021 11155 623 1900

Fiddle

DannySlor0
Related