sql - Accumulated formula in mysql -

i have 3 tables in database. here how looks:

tbl_production:

+--------+------------+-----+-------+   | id_pro | date       | qty | stock |  +--------+------------+-----+-------+   | 1      | 2017-09-09 | 100 |  93   | | 2      | 2017-09-10 | 100 |  100  | 

tbl_out:

+--------+------------+-----+   | id_out | date       | qty |   +--------+------------+-----+ | 1      | 2017-09-09 | 50  |   | 2      | 2017-09-09 | 50  |   | 3      | 2017-09-10 | 50  |   | 4      | 2017-09-10 | 50  | 

tbl_return:

+--------+------------+-----+   | id_out | date       | qty | +--------+------------+-----+ | 1      | 2017-09-09 | 48  |   | 2      | 2017-09-09 | 50  |   | 3      | 2017-09-10 | 60  |   | 4      | 2017-09-10 | 35  | 

i result stock of day. table should be:

+------------+------+   | date       | sotd | +------------+------+ | 2017-09-09 | 98   |   | 2017-09-09 | 193  | 

this result

accumulated stock days before + tbl_production.qty - sum(tbl_out.qty) group date + sum(tbl_return.qty) group date

the stock of date 2017-09-09 0 (because first production) + 100 - 100 + 98 = 98
stock of date 2017-09-10 98 (accumulated stock days before) + 100 - 100 + 95 = 193

i have query this, can't executed

set @running_count := 0; select *,        @running_count := @running_count + qty - (select sum(qty) tbl_out group date) + (select sum(qty) tbl_return group date) counter  tbl_production order id_prod; 

how can result?

in mysql, group by , variables don't work together. try:

select p.date,        (@qty := @qty + qty) running_qty (select p.date, sum(qty) qty       tbl_production p       group p.date      ) p cross join      (select @qty := 0) params order p.date; 

edit:

if want value day before, expression bit complicated, not hard:

select p.date,        (case when (@save_qty := @qty) = null -1  -- never happens              when (@qty := @qty + qty) = null -1  -- never happens              else @save_qty         end) start_of_day (select p.date, sum(qty) qty       tbl_production p       group p.date      ) p cross join      (select @qty := 0) params order p.date;