CODEWITHSUNDEEP
sqlsql-serversql-query-store
Yogesh Sharma
Yogesh Sharma

Reputation: 50173

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.

Upvotes: 0

Views: 106

Answers (4)

SQL006
SQL006

Reputation: 492

select STOCKNO,OP, sum(QTY) OVER(PARTITION BY STOCKNO,OP) as StockLevel
from DT

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81990

Another options (just for fun) is to concat() the OP and Qty

Example

Select STOCKNO
      ,Ttl = sum(convert(decimal(10,2),concat(OP,QTY)))
 from YourTable
 Group By STOCKNO

Returns

STOCKNO Ttl
12345   -15.00
123456  9.00

Upvotes: 0

JohnHC
JohnHC

Reputation: 11205

Use a case inside the sum

select STOCKNO, sum(case when OP = '+' then 1 else -1 end * QTY) as StockLevel
from DT
group by STOCKNO

Upvotes: 0

juergen d
juergen d

Reputation: 204884

SELECT STOCKNO,
       SUM(case when OP = '-' then -QTY else QTY end) as BALANCE
FROM DT 
GROUP BY STOCKNO

Upvotes: 2

Related Questions