CODEWITHSUNDEEP
mysql
vascoder
vascoder

Reputation: 7

SUM and JOIN two tables in MYSQL GROUP BY ID

I have two table INVENTORY and ITEM

inventory Table

| PID |product| Qty | 
|  1  | Bag   | 500 |
|  3  | Cloth | 800 |
|  1  | Bag   | 200 |
|  5  | Cap   | 100 |
|  5  | Cap   | 750 |

item Table

| PID |product| Quantity | 
|  1  | Bag   |    5     |
|  3  | Cloth |    20    |
|  1  | Bag   |    10    |
|  5  | Cap   |    4     |    
|  3  | Cloth |    3     |
|  1  | Bag   |    2     |
|  5  | Cap   |    4     |    
|  5  | Cap   |    8     |

Expected Result

| PID |product| SUM_Inv     | SUM_item|
|  1  | Bag   |    700      |   17    |
|  3  | Cloth |    800      |   23    |
|  5  | Cap   |    850      |   15    |

The problem: am getting wrong answer when I SUM inventory quantity. Please help, I have been trying to solve this issue for weeks now, see code below 

$query=mysqli_query($con,"select i.*, i.PID, i.ProductName, SUM(Qty) as SQty, SUM(quantity) as quantity, t.status 
FROM inventory as i
JOIN items as o ON i.PID=o.PID
JOIN tblorders as t ON o.order_id=t.orderNumber  
WHERE t.status ='Completed'
GROUP BY PID");

Upvotes: 0

Views: 57

Answers (1)

Akina
Akina

Reputation: 42704

SELECT PID, product, SUM(Qty) SUM_Inv, Sum(Quantity) SUM_item
FROM ( SELECT PID, product, Qty, 0 Quantity FROM inventory 
       UNION ALL
       SELECT PID, product, 0, Quantity FROM item ) total
GROUP BY PID, product

Upvotes: 1

Related Questions