Subtract value from group of same table-CodePudding

Year          Month    Code         Value
2021        January     201         100.00
2021        February    201         250.00
2021        January     202         300.00
2021        February    202         200.00
2021        March       201         50.00
2021        March       202         150.00

Need to subtract code 201 from 202 , grouping both code by month. Output :

Year     Month     Value 
2021    january     200 
2021    february    -50
2021     March       100

I was trying to get desired output but its not working..

SELECT Code,Value
    ,
    (
        SELECT sum(Value) as Value
        FROM [TestD]
        Where Code = '202'
        GROUP BY Code
        )-(
        SELECT sum(Value) as Value
        FROM [TestD]
        where Code = '201'
        GROUP BY Code
        )  AS item_total

FROM [TestD] group by Code,Value

CodePudding user response：

You can use a case expression inside sum(...), with group by Year, Month:

select Year, 
Month,
sum(case when Code = '201' then -Value when Code = '202' then Value end) as Value
from TestD
group by Year, Month

Fiddle

CodePudding user response：

You can try this provided only 2 values are going to be subtracted

declare @tbl table (year int, [month] varchar(50),code int, [value] int)

insert into @tbl values(2021, 'January', 201, 100.00),(2021, 'Feb', 201, 250.00)
,(2021, 'January', 202, 300.00),(2021, 'Feb', 202, 200.00)


select year,[month],[value], row_number()over(partition by [month] order by [value] desc) rownum
into #temp
from @tbl

select year,
month
,case when rownum = 1 then value else (
(select value from #temp t1 where t1.[month] = t.[month] and t1.rownum = 1) - 
value) end as diff
from #temp t
where t.rownum = 2
order by month desc

drop table #temp