John Davidson

mysql - Sum of column is not working in join table PHP

0 comments
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.

Rate this post

3 of 5 based on 7521 votes

Comments




© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us