Substract the SUM of orders with the SUM of sales GROUP BY products-CodePudding

I need that the sum of the products sold be subtracted from the sum of the products ordered I've three tables:

Result = (SUM of or_quantity GROUP BY pr_code) - (SUM of sl_quantity GROUP BY pr_code)

How can I do the syntax?


**products**
-------------
id    pr_code
-------------
1     LEGNO1
2     PROD2
3     WOOD

**orders**
---------------------------------------
id    pr_code   or_quantity
---------------------------------------
1     LEGNO1    20
2     LEGNO1    2   
3     PROD2 5   
4     WOOD  8


**sales**
---------------------------------------
id    pr_code   sl_quantity
---------------------------------------
1     LEGNO1    8
2     LEGNO1    1   
3     LEGNO1    1   
4     WOOD  3


**Result**
---------------------------------------
id    pr_code   re_quantity
---------------------------------------
1     LEGNO1    12
2     PROD2 5   
3     WOOD  5

CodePudding user response：

First you have to use UNION ALL between orders & sales and then INNER JOIN with products table.

SELECT      products.id,MIS.pr_code,
            SUM(MIS.or_quantity-MIS.sl_quantity) AS re_quantity
FROM
(
SELECT      pr_code,or_quantity,0 AS sl_quantity
FROM        orders
UNTON ALL
SELECT      pr_code,0 AS or_quantity,sl_quantity
FROM        sales
)           MIS INNER JOIN products ON
            MIS.pr_code=products.pr_code
GROUP BY    products.id,pr_code
ORDER BY    products.id,pr_code;

CodePudding user response：

If i'm correct that what you want is to get sum of product orders and sum of product sales right?

SELECT products.pr_code,
    (SELECT IFNULL(SUM(orders.or_quantity),0) FROM orders WHERE orders.pr_code = products.pr_code) AS sum_orders,
    (SELECT IFNULL(SUM(sales.sl_quantity),0) FROM sales WHERE sales.pr_code = products.pr_code) AS sum_sales,
    (SELECT IFNULL(SUM(orders.or_quantity),0) FROM orders WHERE orders.pr_code = products.pr_code) - (SELECT IFNULL(SUM(sales.sl_quantity),0) FROM sales WHERE sales.pr_code = products.pr_code) as re_quality 
FROM products LEFT JOIN sales
ON (products.pr_code = sales.pr_code) GROUP BY products.pr_code