select - MySql variable sequential calculation with group by do not work properly -
a table following , query give desired result.
select col1, col2, col3, @a:=@a+(col2+col3) col4 test join (select @a:=0)t col1 | col2 | col3 | col4 ------------------------- | 1 | 1 | 2 b | 2 | 0 | 4 c | 3 | 0 | 7 | 0 | 2 | 9
but when use group not work properly. have solution?
select col1, sum(col2)col2, sum(col3)col3, @a:=@a+sum(col2+col3) col4 test join (select @a:= 0)t group col1 col1 | col2 | col3 | col4 ------------------------- | 1 | 3 | 4 b | 2 | 0 | 2 << 6 c | 3 | 0 | 3 << 9
first row fetched correctly. row2 , row3 did not calculate previous row col4 value previous example. can not understand problem!
try code, worked me :)
select col1, col2,col3, @a:=@a+(col2+col3) col4 (select col1,sum(col2)col2,sum(col3)col3 test group col1) tes join (select @a:= 0)t
i made fiddle :
http://sqlfiddle.com/#!2/9b3ac/44
hope helps ;)
Comments
Post a Comment