How to use group by with case statement [closed]

I have a table with following fields

CREATE TABLE Tblstock ( ID int , SlNo int, Storage varchar(10), stock int);

insert into Tblstock values (1, 1, ‘STORE’, 100), (2, 1, ‘Floor 1’, 20), (3, 2, ‘STORE’, 2000), (4, 2, ‘Floor 1’, 40);

I have to dynamically update the left over quantity in store after it got consumed on floor1, I have written a code to calculate qty in store using below mentioned query,

SELECT ( (SELECT CASE WHEN COUNT(B.SlNo) > 1 OR B.Storage = ‘STORE’ THEN SUM(B.Stock)END FROM TblStock B GROUP BY B.SlNo) – (SELECT CASE WHEN COUNT(B.SlNo) > 1 OR B.Storage <> ‘STORE’ THEN SUM(B.Stock)END FROM TblStock B GROUP BY B.SlNo))

However it is not generating the desired result and throwing error

Can anybody help to write it properly so that I get single value of remaining quantity in store

Answer

You just need a straight-forward grouping and conditional aggregation

SELECT
  s.SlNo,
  Total = SUM(CASE WHEN s.Storage = 'STORE' THEN s.Qty ELSE -s.Qty END)
FROM TblStock s
GROUP BY
  s.SlNo;

db<>fiddle