Yogesh Sharma Yogesh Sharma - 1 year ago 32
SQL Question

SQL Query to find Total

I have table structure as below:

STOCKNO QTY OP
12345 1.00 +
12345 25.00 -
12345 1.00 -
12345 10.00 +
123456 10.00 +
123456 1.00 -


I want to get the actual qty balance of each stockno by +, -

+ (i.e. Purchase Qty)
- (i.e. Sales Qty)


But for above I am getting total purchase qty & sales qty using below query:

SELECT STOCKNO, SUM(QTY) as QTY, OP
FROM DT
GROUP BY STOCKNO, OP


AND I want to get the actual closing qty record of each stock.

Answer Source
SELECT STOCKNO,
       SUM(case when OP = '-' then -QTY else QTY end) as BALANCE
FROM DT 
GROUP BY STOCKNO
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download