MySQL Summary in new column -

i have 2 tables consist set of data below , want result in result have calculation in field balance:

i stuck on balance field, how balance running?

tblin

in_date    | code    | in_qty   -----------|---------|--------- 2016-04-01 | aaa     | 100 2016-04-02 | aaa     | 100 2016-04-03 | aaa     | 200 2016-04-06 | aaa     | 400 

tblout

out_date   | code    | out_qty   -----------|---------|--------- 2016-04-02 | aaa     | 100 2016-04-08 | aaa     | 400 

result

date       | code    | in_qty   | out_qty  | balance -----------|---------|----------|----------|--------- 2016-04-01 | aaa     | 100      | 0        | 100 2016-04-02 | aaa     | 100      | 0        | 200 2016-04-02 | aaa     | 0        | 100      | 100 2016-04-03 | aaa     | 200      | 0        | 300 2016-04-06 | aaa     | 400      | 0        | 700 2016-04-08 | aaa     | 0        | 400      | 300 

query (thanks @1000111)

select  t.*, @prevbalance := (t.in_qty - t.out_qty) + ifnull(@prevbalance,0) balance  (     select          in_date date,         code,         in_qty,         0 out_qty     tblin      union       select          out_date,         code,         0,         out_qty     tblout ) t , (select @prevbalance := null) var order t.date; 

this query result in result, if want kind of result. mysql summary upto 2016-04-03 single line , continue detail next date.

result1

date       | code    | in_qty   | out_qty  | balance -----------|---------|----------|----------|--------- 2016-04-03 | aaa     | 0        | 0        | 300 2016-04-06 | aaa     | 400      | 0        | 700 2016-04-08 | aaa     | 0        | 400      | 300 

try query.. tested 2016-04-02 feel free change 1 line commented change date date like

select @startdate date,        -- below determine code since code has come somewhere        -- we'll select code of latest date before        -- or equal @startdate        (select tcode.code           (select code,in_date tblin   in_date <= @startdate            union            select code,out_date tblout out_date <= @startdate)tcode          order tcode.in_date desc         limit 1         ) code,         0,0,         balance                  (select @prevbalance :=(select sum(in_qty) tblin, -- change date in below line date desire @startdate used throughout whole query                                 (select @startdate := date('2016-04-02'))tdate in_date <= @startdate)-                                (select sum(out_qty) tblout out_date <= @startdate) balance)t4 union select *   (select t.*,@balance := @balance + (t.in_qty - t.out_qty) balance         (select in_date date,code,in_qty,0 out_qty tblin in_date > @startdate      union      select out_date,    code,     0,     out_qty tblout out_date > @startdate     )t,(select @balance:=@prevbalance)initial    order t.date asc,t.in_qty desc   )t3; 

sqlfiddle

update prevent null use ifnull()

select @startdate date,        -- below determine code since code has come somewhere        -- we'll select code of latest date before        -- or equal @startdate        (select tcode.code           (select code,in_date tblin   in_date <= @startdate            union            select code,out_date tblout out_date <= @startdate)tcode          order tcode.in_date desc         limit 1         ) code,         0,0,         balance                  (select @prevbalance :=ifnull((select sum(in_qty) tblin, -- change date in below line date desire @startdate used throughout whole query                                                                (select @startdate := date('2016-03-02'))tdate in_date <= @startdate)-                                       (select sum(out_qty) tblout out_date <= @startdate),0                                      )                                 balance         )t4 union select *   (select t.*,@balance := @balance + (t.in_qty - t.out_qty) balance         (select in_date date,code,in_qty,0 out_qty tblin in_date > @startdate      union      select out_date,    code,     0,     out_qty tblout out_date > @startdate     )t,(select @balance:=@prevbalance)initial    order t.date asc,t.in_qty desc   )t3;