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

Question

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.

Answer 1

you should normalize your tbales the columns order_items.type and orders.order_type should be Tinyint and you need also two small tables ordertype and orderItemtype that canes joined when the text is needed

Schema (MySQL v5.7)

CREATE TABLE order_items (
  `id` INTEGER,
  `order_id` INTEGER,
  `item_id` INTEGER,
  `quantity` INTEGER,
  `type` VARCHAR(6)
);

INSERT INTO order_items
  (`id`, `order_id`, `item_id`, `quantity`, `type`)
VALUES
  ('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'),
  ('10', '4', '1', '5', 'Accept'),
  ('11', '4', '2', '10', 'Accept');

CREATE TABLE orders (
  `id` INTEGER,
  `order_type` VARCHAR(8),
  `order_from` INTEGER,
  `base_order_if_purchase` VARCHAR(4),
  `amount` INTEGER,
  `status` VARCHAR(10)
);

INSERT INTO orders
  (`id`, `order_type`, `order_from`, `base_order_if_purchase`, `amount`, `status`)
VALUES
  ('1', 'sales', '1', NULL, '8500', 'incomplete'),
  ('2', 'purchase', '1', '1', '2500', 'complete'),
  ('3', 'purchase', '1', '1', '5000', 'complete'),
  ('4', 'sales', '1', NULL, '7000', 'incomplete');

---

Query #1

SELECT 
    `row_num`,`item_id`
    ,SUM(`sumitem`)
   FROM 
   (SELECT 
         `item_id`,o.`row_num` , IF (`order_type` = 'sales',`quantity`,-`quantity`) sumitem
    FROM order_items o_i 
   INNER JOIN (  SELECT o.`id`, o.`order_type`,row_num
  FROM `orders` o INNER JOIN (
  SELECT `id`, @rn := @rn + 1  row_num FROM orders ,(SELECT @rn := 0) a WHERE `base_order_if_purchase` IS NULL ORDER BY `id`) o1 
  ON o.`id` =  o1.`id` OR o.`base_order_if_purchase` =  o1.`id`) o ON o_i.`order_id` = o.`id`
   WHERE o_i.`type` = 'Accept') t1
  GROUP BY `row_num`,`item_id`;

| row_num | item_id | SUM(`sumitem`) |
| ------- | ------- | -------------- |
| 1       | 1       | 3              |
| 1       | 2       | 0              |
| 1       | 4       | -10            |
| 2       | 1       | 5              |
| 2       | 2       | 10             |

---

View on DB Fiddle