I have a table users and other table user_scores. In user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG user_score by only considering latest 10 records of user_score in user_scores table. I have tried following two queries but not successful with these.


UPDATE      user
SET         user.user_score = (
    SELECT AVG(score) as avg_score FROM (
        SELECT score FROM user_scores WHERE user_id = user.id ORDER BY id DESC LIMIT 0,10)as x)

I am facing error Unknown column 'user.id' in 'where clause' cuz I am not able to pass variable to subquery of the query

  1. Other thing I have tried by JOIN but in JOIN I am not able to calculate AVG(score) of each user based on latest 10 records.

Please suggest.

  • I think there is a rule that prohibits passing a column through more than one level of subqueries.
    – Rick James
    Mar 12, 2018 at 21:46

3 Answers 3

SET user_score = (
    SELECT AVG(score) 
    FROM (
        SELECT score 
        FROM user_scores 
        WHERE user_id = user.id 
        ORDER BY id DESC 
        LIMIT 10
    ) AS latest_scores

This query first selects the latest 10 scores for each user from the user_scores table using a subquery. It then calculates the average of these scores and updates the user_score column in the user table for each respective user.


I may have a solution, but first please see if this computes the avg_scores correctly:

        user_id, AVG(score) AS avg_score
      ( SELECT  @prev := 0, @n := 0 ) init
      ( SELECT  @n := if(user_id != @prev, 1, @n + 1) AS n,
                @prev := user_id,
                user_id, score, id
            FROM  user_scores
            ORDER BY
                user_id  DESC,
                id  DESC
      ) x
    WHERE  n <= 10
    GROUP BY user_id;

Recommend INDEX(user_id, id, score) on user_scores .

If that works, then a multi-table UPDATE may finish the task:

UPDATE   user
    JOIN ( the above ) AS y  USING(user_id)
    SET  user.user_score = y.avg_score;

I created one function to handle this part, this way I could pass value into function and get user_score from function

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.