title: mysql: group functions on subqueries with limits
body: <p>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.</p>
  
  <p>user table: userid, username, usertype<br>
  task table: taskid, score, tasktype, userid</p>
  
  <p>If I do:</p>
  
  <pre><code>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';
  </code></pre>
  
  <p>that doesn't work because it does the limit 50 after it does the average of everything.</p>
  
  <p>What I need is this:</p>
  
  <pre><code>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';
  </code></pre>
  
  <p>but that is not valid syntax</p>
  
  <p>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.</p>
  
  <p>Is there a way to do this?</p>