John Davidson

Message:

I am trying to calculate the req_qty and in_qty from table1 and table2.
I am trying to fetch the joined 3 tables data SUM(req_qty), SUM(in_qty). Issue come if req.qty not save in table2 it will not works. I am left joining the table by this sku's ids

I made here a very simple table for calculating the sum of the column. generally, the sum of the column is multiplying the total rows.
Please don't close the post and guild for other thread. for my doubt clear i made here a table which I can understand. please help.

table1



ID  |  sku_1 | req_qty | trans_id

----------------------------------

1   |  123   | 150     | 12345

2   |  142   | 200     | 256314

3   |  123   | 100     | 896523

table2

ID  | sku_2  | in_qty  | trans_key

-----------------------------------

1   | 142    | 50      | 002563

table3

ID |  sku_code  |  sku_name

--------------------------

1  | 142        |  ABC

2  | 123        |  XYZ

Expected Output

ID | sku | sku_name | reqQty | inQty 

------------------------------------

1  | 123 | XYZ      | 250    | 0        

2  | 142 | ABC      | 200    | 50

---

My Answer:
You can achieve the desired output by using a LEFT JOIN and GROUP BY in your SQL query. Here is an example query that should work for your case:

sql
SELECT t1.ID, t1.sku_1 AS sku, t3.sku_name, SUM(t1.req_qty) AS reqQty, COALESCE(SUM(t2.in_qty), 0) AS inQty
FROM table1 t1
LEFT JOIN table2 t2 ON t1.sku_1 = t2.sku_2
LEFT JOIN table3 t3 ON t1.sku_1 = t3.sku_code
GROUP BY t1.sku_1


In this query:
- We are selecting the ID from table1, sku from table1 as sku, sku_name from table3, and calculating the SUM of req_qty from table1 and in_qty from table2.
- We are using LEFT JOIN to join table1 with table2 and table3 based on the sku values.
- We are using GROUP BY to group the results by sku.
- We are using COALESCE to handle cases where in_qty is NULL (i.e., when there is no corresponding record in table2 for a sku).

This query should give you the expected output with the sum of req_qty and in_qty for each sku.