CODEWITHSUNDEEP
mysql
Varun Jain
Varun Jain

Reputation: 507

Mysql stock calculation query

I have below table data 

Product Reason Qty
Pepsi   IN     10
Pepsi   Out    2
Pepsi   In     15
Pepsi   Out    5 
Coke    IN     100
Coke    Out    20
Coke    In     35
Coke    Out    25
Fanta   Out     55

and I want to get result like below:

Product Qty
Pepsi   18
Coke    90
Fanta   -55

I have written below query but I am not getting Fanta in output.

select indata.Name,indata.PRODUCTS_ID, (indata.QTY - outdata.QTY) as `QTY` from
(
(select 
    distinct
    stock.PRODUCTS_ID,
    stock.Name,
    stock.QTY
    from stock where reason in ('IN','REFUND') 
    ) indata,
(select 
    distinct
    stock.PRODUCTS_ID,
    stock.Name,
    stock.QTY
    from stock where reason in ('OUT','WASTE') 
) outdata
);

Upvotes: 1

Views: 232

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

It looks like a good candidate for conditional aggregation:

SELECT Product,
       SUM(CASE WHEN reason IN ('IN','REFUND') THEN Qty 
                WHEN reason IN ('OUT','WASTE') THEN -Qty
                ELSE NULL END) AS Qty
FROM stock
GROUP BY Product;

LiveDemo

Upvotes: 2

Related Questions