CODEWITHSUNDEEP
mysqlsql
M Muqiit Faturrahman
M Muqiit Faturrahman

Reputation: 129

How to calculate stock in 2 tables

I have the first table like this (tbl_med)

id_med  name    stock
1       abc      10
2       bcd      15
3       efd      11

And the second table like this (tbl_trans)

id_trans   id_med  qty
1          1       4      
2          1       2      
3          2       3

How to calculate last stock or left stock in the firs table? I tried this but it's not show left stock for id_med 3

SELECT tbl_med.stock, tbl_med.id_med, tbl_med.stock - SUM(tbl_trans.qty) AS stock_left FROM tbl_med
JOIN tbl_trans ON  tbl_med.id_med= tbl_trans.id_med
GROUP BY tbl_med.id_med

Upvotes: 1

Views: 129

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I think you just need coalesce() and left join:

SELECT m.stock, m.id_med, m.stock - COALESCE(SUM(t.qty), 0) AS stock_left
FROM tbl_med m LEFT JOIN
     tbl_trans t
     ON m.id_med = t.id_med
GROUP BY m.id_med;

Note that usually having unaggregated columns in the SELECT that are not GROUP BY keys is an error in SQL. However, it works fine if the GROUP BY has the primary key of the table. I assume id_med is the primary key of tbl_med.

Upvotes: 2

Related Questions