MySQL calculate from same value -

MySQL calculate from same value -

i have query:

select `item_code`, `q_rr`, `q_srs`, @running_bal := @running_bal + (`q_rr` - `q_srs`) `balance` records, (select @running_bal := 0) tempname order records.item_code, records.date

which results is:

item_code | q_rr | q_srs | balance -------------------------------------------- 0f02206a 2 0 2 br00113d 3 0 5 br00114d 10 0 15 br00114d 0 1 14 br00114d 0 1 13 br00115d 20 0 33 br00115d 0 1 32 br00115d 0 1 31

need help create result calculate balance, if q_rr(+) , q_srs(-) , calculate per item_code.

item_code | q_rr | q_srs | balance -------------------------------------------- 0f02206a 2 0 2 br00113d 3 0 3 br00114d 10 0 10 br00114d 0 1 9 br00114d 0 1 8 br00115d 20 0 20 br00115d 0 1 19 br00115d 0 1 18

i added @code variable track item_code changes:

select r.item_code, r.q_rr, r.q_srs, @running_bal := if(@code = r.item_code, @running_bal, 0) + (r.q_rr - r.q_srs) balance, @code := r.item_code dummy records r cross bring together (select @running_bal := 0, @code := '') tempname order r.item_code, r.date

sql fiddle: http://sqlfiddle.com/#!2/04ef2b/11

you can eliminate dummy column putting subquery in select:

select item_code, q_rr, q_srs, balance ( -- there set first query ) r

sql fiddle: http://sqlfiddle.com/#!2/04ef2b/12

mysql