How to get balance of of remaining medicine batch wise I have this query:
select itemcode, batchno, eXpirydate,
    CASE
         WHEN type= 1 THEN sum(qty)                   
         WHEN type= 2 THEN sum(qty*-1)                
    END AS balance 
FROM strans   
group by itemcode, batchno, EXpirydate,type

It gives me these results:
   itemcode  batchno    EXpirydate    balance 
1  0000002  123         2023-01-01     500
2  0000002  123         2023-01-01    -100
3  0000002  321         2024-01-01     500
4  0000002  321         2024-01-01    -150

I want to get the following results:
   itemcode  batchno    EXpirydate    balance 
1  0000002  123         2023-01-01     400
2  0000002  321         2024-01-01     350
Basically you cannot GROUP BY the type, because you want the  SUM of both types
SELECT itemcode, batchno, eXpirydate,
    SUM(CASE
         WHEN type= 1 THEN qty
         WHEN type= 2 THEN qty*-1
    END) AS balance 
FROM strans   
GROUP BY itemcode, batchno, EXpirydate