Merge Multiple Queries Into 1 Query – On the same Row

I have 1 table called itemmovement : It has Item Id , Quantity In , Quantity Out , Invoice Id, Date. I need to make in one query to show how many pieces are sold and beside the sold column there will be the current on hand quantity .

itemmovement

Id   itemid      qtyin     qtyout    invid        date
1     1            10                          2019-01-04
2     2            8                           2019-01-06
3     2                      2         1       2019-01-08
4     1                      3         2       2019-01-12
5     2            1                           2019-02-04
6     3            4                           2019-03-04
7     1                      1         3       2019-04-04

I need the query to show this result

Id   itemid      Sold Quantity   OnHandQty
1     1               4             6
2     2               2             7
3     3               0             4

I’m Trying to use this query but not working

SELECT * from (
        SELECT itmv.itemid,sum(itmv.qtyout)-sum(itmv.qtyin)
        FROM itemmovement itmv
        WHERE   ( itmv.systemdate BETWEEN '2019-01-01' AND '2019-06-01') AND 
                invid>0 
        group by itmv.itemid) as result1,

        (SELECT sum(itmv2.qtyin)-sum(itmv2.qtyout)
        from itemmovement itmv2
        where itmv.itemid=itmv2.itemid
        group by itmv2.itemid) as result2

        order by sum(itmv.qtyin)-sum(itmv.qtyout)

Answer

Do you just want aggregation?

select itemid,
       sum(qtyout) as sold_quantity,
       coalesce(sum(qtyin), 0) - coalesce(sum(qtyout), 0) as onhand
from itemmovement itmv
group by itemid;

Advertisement

Answer