Reputation: 1

Query to find the stock

How To find the stock..?

I have a table "Transactions" in which there are fields like ProductID(int), TransactionType(boolean), Qty(int)..

While we are purchasing, TransactionType is True, And while we are Selling, TransactionType is False..

Id          Type          Qty  
1           true          3  
1           true          9  
1           False         2

If I buyed 12 qty of productId 1,
and Sold 2 Qty of productId 1,
then How can I get the stock remaining ...?

Upvotes: 0

Reputation: 204884

select ProductID,
       sum(iif(TransactionType = 1, Qty, -Qty)) as total
from Transactions
group by ProductID

Upvotes: 1

Reputation: 95072

You calculate the sum, but instead of summing Qty, you need a case statement to interpret Qty as positive or negative:

select id, sum(Qty * case when type = 'true' then 1 else -1 end) as sum_qty
from transactions
group by id;

Upvotes: 0

Reputation: 6543

Try this :

SELECT ProductId,
       SUM(CASE WHEN TransactionType = 1 THEN Qty ELSE -Qty END)
FROM Transactions
GROUP BY ProductId

Upvotes: 1