Use of Sum with Group By when using Left Join for multiple tables

Issue

I have 4 tables to join.

Sub_Category

ID NAME
1 Monitor
2 Laptop
3 Mouse

Products

ID NAME SUBCATEGORY_ID AVAILABLE_QTY
1 LG 1 4
2 Dell 1 5
3 HP Probook 2 2
4 Asus 1 3

Orders

ID PRODUCTS_ID ORDER_QTY USER_ID
1 1 2 1
2 1 1 2
3 2 3 4

Dead Stock (Damaged Products)

ID PRODUCTS_ID Dead_qty
1 1 2
2 1 1
3 2 1

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.

Sub_category_name Available_qty (sum) Order_qty Dead_qty
Monitor 12 6 3
Laptop 2 1
Mouse

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:

Sub_category_name Available_qty (sum) Order_qty Dead_qty
Monitor 12 3 3
Laptop 0
Mouse 0

Solution

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:

Result Set_00000

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

Answered By – Ozan Sen

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published