I have 4 tables to join.

Sub_Category

Products

Orders

Dead Stock (Damaged Products)

I want the sum of all product quantity (qty) of each subcategory along with the sum of orders quantity and dead stock quantity that relates to that individual subcategory.

My query that is not working:

select
    s.id, s.name, COALESCE(sum(p.qty),0) produtcs_avail,
    d1.orderqty, d2.deadqty
from subcategories s 
left join products p on p.subcategory_id= s.id 
left join (select oi.product_id, sum(oi.qty) as orderqty
    from orders_item oi
    group by oi.product_id
    ) d1 on d1.product_id=p.id 
left join (select d.product_id, sum(d.qty) as deadqty
    from dead_stocks d
    group by d.product_id
    ) d2 on d2.product_id=p.id 
group by s.id

It returns:

CodePudding user response：

CREATE TABLE subcategories (ID INT, NAME VARCHAR(20));
INSERT INTO subcategories VALUES
(1,'Monitor'),(2,'Laptop'),(3,'Mouse');

CREATE TABLE products (ID INT, NAME VARCHAR(30),SUBCATEGORY_ID INT, AVAILABLE_QTY INT);
INSERT INTO products VALUES
(1,'LG',1,4),   
(2,'Dell',1,5),
(3,'HP Probook',2,2),
(4,'Asus',1,3);

CREATE TABLE orders_item (ID INT, PRODUCTS_ID INT, ORDER_QTY INT, USER_ID INT);
INSERT INTO orders_item VALUES
(1,1,2,1),
(2,1,1,2),
(3,2,3,4);

CREATE TABLE dead_stocks (ID INT,PRODUCTS_ID INT,Dead_qty INT);
INSERT INTO dead_stocks VALUES
(1,1,2),
(2,1,1),
(3,2,1);

SELECT 
    s.name AS sub_category_name, 
    SUM(DISTINCT p.AVAILABLE_QTY) AS available_qty, 
    sum(DISTINCT oi.ORDER_QTY) AS orders_qty, 
    sum(DISTINCT d.Dead_qty) AS dead_qty  
FROM subcategories s 
LEFT JOIN products p 
    ON s.id=p.subcategory_id 
LEFT JOIN orders_item oi 
    ON p.id=oi.PRODUCTS_ID
LEFT JOIN dead_stocks d
    ON p.id=d.PRODUCTS_ID 
GROUP BY s.name
ORDER BY available_qty DESC;

Result:

Note: dead_qty evaluates to 3 in my result set; but your result set has 4.