sql - Conditional cumulative SUM in MySQL -

i have following table:

+-----+-----------+----------+------------+------+ | key | idstudent | idcourse | hourcourse | mark | +-----+-----------+----------+------------+------+ |   0 |         1 |        1 |         10 |   78 | |   1 |         1 |        2 |         20 |   60 | |   2 |         1 |        4 |         10 |   45 | |   3 |         3 |        1 |         10 |   90 | |   4 |         3 |        2 |         20 |   70 | +-----+-----------+----------+------------+------+ 

using simple query, can show student weighted average according hourcourse , mark:

select idstudent,        sum( hourcourse * mark ) / sum( hourcourse ) weightedavg   `test`.`test`   group idstudent; +-----------+-------------+ | idstudent | weightedavg | +-----------+-------------+ |         1 |     60.7500 | |         3 |     76.6667 | +-----------+-------------+ 

but need select registers until cumulative sum of hourcourse per student reaches threshold. example, threshold of 30 hourcourse, following registers should taken account:

+-----+-----------+----------+------------+------+ | key | idstudent | idcourse | hourcourse | mark | +-----+-----------+----------+------------+------+ |   0 |         1 |        1 |         10 |   78 | |   1 |         1 |        2 |         20 |   60 | |   3 |         3 |        1 |         10 |   90 | |   4 |         3 |        2 |         20 |   70 | +-----+-----------+----------+------------+------+ 

key 2 not taken account, because idstudent 1 reached 30 hourcourse idcourse 1 , 2.

finally, query solution should following:

+-----------+-------------+ | idstudent | weightedavg | +-----------+-------------+ |         1 |     66.0000 | |         3 |     76.6667 | +-----------+-------------+ 

is there way create inline query this? in advance.

edit: criteria while selecting courses highest lowest mark. edit: registers included while cumulative sum of hourcourse less 30. instance, 2 registers of 20 hours each included (sum 40), , following not.

you can calculate cumulative sums per idstudent in sub-query, select results cumulative sum <= 30:

select idstudent,        sum( hourcourse * mark ) / sum( hourcourse ) weightedavg (   select t.*,   case when @idstudent<>t.idstudent     @cumsum:=hourcourse     else @cumsum:=@cumsum+hourcourse   end cumsum,   @idstudent:=t.idstudent   `test` t,   (select @idstudent:=0,@cumsum:=0) r   order idstudent, `key` ) t t.cumsum <= 30 group idstudent; 

demo: http://www.sqlfiddle.com/#!2/f5d07/23