1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to group by items and calculate the quantities after calculation MySQL

Discussion in 'Programming/Internet' started by camille, Sep 16, 2020.

  1. camille

    camille Guest

    I have a set of order items placed in my table. These order items are generated from 2 order types. The sales order and the purchase order. One sales order can have multiple purchase orders to fulfill the quantities. I want a way to calculate the remaining quantity to fulfill the next sales order in database level.

    this is my order table

    +----+------------+------------+------------------------+--------+------------+
    | id | order_type | order_from | base_order_if_purchase | amount | status |
    +----+------------+------------+------------------------+--------+------------+
    | 1 | sales | 1 | null | 8500 | incomplete |
    +----+------------+------------+------------------------+--------+------------+
    | 2 | purchase | 1 | 1 | 2500 | complete |
    +----+------------+------------+------------------------+--------+------------+
    | 3 | purchase | 1 | 1 | 5000 | complete |
    +----+------------+------------+------------------------+--------+------------+


    The sales order will be completed after the quantities has been fulfilled in all purchase orders.

    This is my order_items table

    +----+----------+---------+----------+--------+
    | id | order_id | item_id | quantity | type |
    +----+----------+---------+----------+--------+
    | 1 | 1 | 1 | 10 | Accept |
    +----+----------+---------+----------+--------+
    | 2 | 1 | 2 | 15 | Accept |
    +----+----------+---------+----------+--------+
    | 3 | 1 | 3 | 20 | Reject |
    +----+----------+---------+----------+--------+
    | 4 | 2 | 1 | 5 | Accept |
    +----+----------+---------+----------+--------+
    | 5 | 2 | 2 | 10 | Accept |
    +----+----------+---------+----------+--------+
    | 6 | 2 | 4 | 10 | Accept |
    +----+----------+---------+----------+--------+
    | 7 | 3 | 1 | 2 | Accept |
    +----+----------+---------+----------+--------+
    | 9 | 3 | 2 | 5 | Accept |
    +----+----------+---------+----------+--------+


    Order 1 is the sales Order. Order 2 & 3 are purchase orders. I want to calculate the remaining quantities of the items. Some new items also can be added in the purchase order which are not in the sales order.

    Accepted Order 1 items - (Order 2 items + Order 3 items)


    So the result should be

    +---------+----------+
    | item_id | quantity |
    +---------+----------+
    | 1 | 3 |
    +---------+----------+
    | 2 | 0 |
    +---------+----------+
    | 4 | 10 |
    +---------+----------+


    I'm currently doing this by selecting the items in each order and performing the calculation in application level.

    Login To add answer/comment
     

Share This Page