This is what my table looks like:

I want the result to look like this:

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?

CodePudding user response：

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

CodePudding user response：

select   RefNum
        ,Year
        ,CorrespVNum
        ,sum(Proceeds) as TotalProceeds 
        ,sum(BaseCost) as TotalBaseCost
from     t
group by RefNum, Year, CorrespVNum

Fiddle