Subtract in single table

amir0914

Registered User.
Local time
Yesterday, 16:01
Joined
May 21, 2018
Messages
151
Hi all, I have a table that I enter count of each product every day and the products may enter or exit from our warehouse, now I need a SQL code to return total products that we have in the warehouse, like this :
A1​
in​
15​
A1​
in​
16​
A1​
out​
5​
A2​
in​
4​
A2​
out​
2​
A3​
in​
10​
A3​
in​
5​

Result : (total product in the warehouse)
A1​
26​
A2​
2​
A3​
15​
 
Try

SELECT Product, Sum(IIf(Code = "in", Quantity, -Quantity)) As NetTotal
FROM TableName
GROUP BY Product

Changing the table/field names in red as appropriate.
 
@amir0914, I think your question is a bit ambiguous. Is that what you HAVE? Or is that what you WANT TO SEE?

Show us something of your data structure because I don't see what we have to work with here.

The "normal" way to do inventories is you have transactions that show you product ID, quantity moved, transaction type (I.e. stock add, stock draw, etc.), transaction date. Then you do a query to sum the quantity moved and group by the product ID and order by the date of transaction. The trick is that the transaction type is a code that lets you multiply quantity moved by -1 if it is any type that removes from stock and leaves it as positive if it is something that increases stock quantity.
 
Paul's solution is easy. and it seems like it should work just fine because he doing record by record math, even though it's not transacting that way, technically. have you looked at the northwind example of inventory management by microsoft? might be of some use in this case.
 
Try

SELECT Product, Sum(IIf(Code = "in", Quantity, -Quantity)) As NetTotal
FROM TableName
GROUP BY Product

Changing the table/field names in red as appropriate.
Your way worked fine, thank you very much.
 
Last edited:
Paul's solution is easy. and it seems like it should work just fine because he doing record by record math, even though it's not transacting that way, technically. have you looked at the northwind example of inventory management by microsoft? might be of some use in this case.
No I haven't seen sample files of microsoft, but my problem has been resolved by pbaldy code.
 

Users who are viewing this thread

Back
Top Bottom