subquery - mysql: group functions on subqueries with limits -

i have group of users perform tasks on scored. i'm trying create report showing average of each user's last 50 tasks.

user table: userid, username, usertype
task table: taskid, score, tasktype, userid

if do:

select u.userid, u.username, (select avg(score)                             task t                             t.userid = u.userid , t.tasktype = 'task1'                             order t.taskid desc limit 50) avgscore user u u.usertype = 'utype'; 

that doesn't work because limit 50 after average of everything.

what need this:

select u.userid, u.username, avg(select t.score                             task t                             t.userid = u.userid , t.tasktype = 'task1'                             order t.taskid desc limit 50) avgscore user u u.usertype = 'utype'; 

but not valid syntax

i've tried sub-sub queries, can't way either, problem limit, or join, or unknown fields when reference u.userid in sub-subquery.

is there way this?

use subquery within subquery:

select u.userid, u.username,        (select avg(score)         (select t.*               task t               t.userid = u.userid , t.tasktype = 'task1'               order t.taskid desc               limit 50              ) t        ) avgscore user u u.usertype = 'utype'; 

edit:

i didn't realize mysql not recognize u.userid. should according ansi rules scoping of table aliases.

you can take different approach find 50th taskid value, , take above that:

select ut.userid, ut.username, avg(t.score) (select u.userid, u.username,              (select substring_index(substring_index(group_concat(taskid order taskid desc                                                                  ), ',', 50), ',', -1)               task t               t.userid = u.userid , t.tasktype = 'task1'              ) + 0 taskid50       user u       u.usertype = 'utype'      ) ut join      task t      on ut.userid = t.userid ,         ut.taskid50 >= t.taskid , t.tasktype = 'task1' group ut.userid, ut.username;