I want to sum and join 2 tables. I am getting a wrong answer. What am i doing wrong please
$stmt = $pdo->query("
SELECT
a.prod_name, a.prod_size,
b.prod_name, b.prod_size,
SUM(b.qty_bought) qty_bot,
SUM(a.prod_qty) qty_received
FROM tbl_distribution a
JOIN tbl_sales_bar b
ON a.staff_id = b.staff_id
WHERE a.staff_id = '$_GET[id]'
GROUP BY b.prod_id
");
WHILE($row = $stmt->fetch(PDO::FETCH_ASSOC)){
echo '<tr>
<td>'.$row["prod_name"].'</td>
<td>'.$row["prod_size"].'</td>
<td>'.$row["qty_received"].'</td>
<td>'.$row["qty_bot"].'</td>
<td></td>
</tr>';
}
what i want is to get the sum of quantity bought and the sum of product quantity so that when i minus the product quantity for the quantity bought, i will get the balance which is the difference between the product quantity - quantity bought = available quantity
Thanks
Edit: let me use images to show what i intend to have as result:
thanks