Monday, 19 August 2013

mysql: group functions on subqueries with limits

mysql: group functions on subqueries with limits

I have a group of users who perform tasks on which they are scored. I'm
trying to create a report showing the average of each user's last 50
tasks.
user table: userid, username, usertype
task table: taskid, score, tasktype, userid
If I do:
SELECT u.userid, u.username, (SELECT AVG(score)
FROM task t
WHERE t.userid = u.userid AND t.tasktype =
'task1'
ORDER BY t.taskid DESC LIMIT 50) AS avgscore
FROM user u
WHERE u.usertype = 'utype';
that doesn't work because it does the limit 50 after it does the average
of everything.
What I need is this:
SELECT u.userid, u.username, AVG(SELECT t.score
FROM task t
WHERE t.userid = u.userid AND t.tasktype =
'task1'
ORDER BY t.taskid DESC LIMIT 50) AS avgscore
FROM user u
WHERE u.usertype = 'utype';
but that is not valid syntax
I've tried sub-sub queries, but can't get it that way either, as I always
get a problem with the limit, or a join, or unknown fields when I
reference u.userid in the sub-subquery.
Is there a way to do this?

No comments:

Post a Comment