sql - Running Sums for Multiple Categories in MySQL -

i have table of form

 category      time      qty                  1        20       b           2         3                3        43                4        20      b           5        25 

i need running total calculated category in mysql. result this:

 category      time      qty     cat.total                  1        20         20      b           2         3          3                3        43         63                4        20         83      b           5        25         28 

any idea how efficiently in mysql? have searched far , wide, can find info on how insert 1 single running total in mysql. wonder if there's way use group or similar construct achieve this.

you calculate sum in subquery:

select  category ,       time ,       qty ,       (         select  sum(qty)             yourtable t2            t1.category = t2.category                  , t1.time >= t2.time         ) cattotal    yourtable t1 

trading readability speed, can use mysql variable hold running sum:

select  category ,       time ,       qty ,       @sum := if(@cat = category,@sum,0) + qty cattotal ,       @cat := category    yourtable cross join         (select @cat := '', @sum := 0) initvarsalias order         category ,       time 

the ordering required construct work; if need different order, wrap query in subquery:

select  category ,       time ,       qty ,       cattotal    (         select  category         ,       time         ,       qty         ,       @sum := if(@cat = category,@sum,0) + qty cattotal         ,       @cat := category            yourtable         cross join                 (select @cat := '', @sum := 0) initvarsalias         order                 category         ,       time         ) subqueryalias order         time