How to use group by with case statement-CodePudding

I have a table with following fields

ID	SlNo	Storage	Qty
1.	1.	STORE.	100
2.	1.	Floor 1.	20.
3.	2.	STORE.	2000
4.	2.	Floor 1.	40.

I have written code for calculating balance in store like this

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))

But it's not working in MsSql

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

CodePudding user response：

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

CodePudding user response：

Assuming what you are trying to do is to deduct the quantity (qty) in storage called store by the sum of the rest of the other storage. I could think of a query like this:

 select *,
     (Qty - (select sum(b.Qty) from tblstock as b
             where b.Storage <> 'store'
                   and b.SINo = a.SINo
             group by b.SINo)) as remainingQty
 from tblstock as a
 where a.Storage = 'store' group by a.SINo

The query above, with the following input:

ID	SINo	Storage	Qty
1	1	store	100
2	1	floor 1	20
3	1	floor 2	30
4	2	store	100
5	2	floor 1	40
6	2	floor 2	50

It produces the following output:

ID	SINo	Storage	Qty	remainingQty
1	1	store	100	50
4	2	store	100	10

You can find the SQLFiddle here.

Note:

If you are want to avoid subquery and have the urge to chug in join fiddle:

select a.id,
    a.SINo,
    a.Storage,
    a.Qty,
    c.Qty,
    (a.Qty - c.Qty) as remainingQty
from tblstock as a
join
     (select b.SINo,
             sum(b.Qty) as Qty
       from tblstock as b
       where b.Storage <> 'store'
       group by b.SINo) as c
on c.SINo = a.SINo
where a.Storage = 'store' group by a.SINo