Sum from other table

[fvgb]

I have a MySQL database with two tables (USER and SCORE). Is it possible to update the USER table (i.e. calculate the average scores from the SCORE table for all users in table USER) in one MySQL query or do I have to run a PHP loop?

USER
username average_score
john 50
fred 100

SCORE
username score
john 100
john 25
john 25
fred 50
fred 150

[r937]

Is it possible to update the USER table (i.e. calculate the average scores from the SCORE table for all users in table USER) in one MySQL query ...

yes, it is

but why would you need to do that? you can grab the average score any time you want it right off the score table

if you update the user table, it'll soon fall out of date

which means you would need to re-update the average as soon as a user has a new score

seems like a lot of needless extra processing

[fvgb]

Thank you for the feedback.

I've been going in the wrong direction.

However, as I have many users and each user is shown the average scores of the top 50 users I still need to save the averages in a separate field otherwise averages for all users would need to be calculated everytime a user wants to see the averages. I think it's better to just calculate the average for the individual user when he sets a new score and save this in a separate field from which a simple select can fetch the information when needed?

[r937]

...each user is shown the average scores of the top 50 users

whoa

okay, i can see the need to store the averages now, but just remember my warning about how easily they will get out of date

UPDATE user
INNER
  JOIN ( SELECT username
              , AVG(score) AS avg_score
          FROM score
         GROUP
             BY username ) AS averages
    ON averages.username = user.username
   SET user.average_score = averages.avg_score

[SpacePhoenix]

Thank you for the feedback.

I've been going in the wrong direction.

However, as I have many users and each user is shown the average scores of the top 50 users I still need to save the averages in a separate field otherwise averages for all users would need to be calculated everytime a user wants to see the averages. I think it's better to just calculate the average for the individual user when he sets a new score and save this in a separate field from which a simple select can fetch the information when needed?

SELECT
      username AS name
    , AVG(score) AS avg_score
FROM
    score
GROUP BY
    username

By "top 50 users" is that the top 50 by average score?

[fvgb]

As I now plan to update a players' average (but not other players') everytime he scores I think all averages will always be updated.

Thank you very much for the code! As I only want to update for the active player I modified it somewhat and it seems to function correctly. Example below for player john (I have 57 different scores and want the total divided by this number even if a player lacks some of the scores).


UPDATE user
INNER
JOIN ( SELECT username
, SUM(score)/57 AS avg_score
FROM score WHERE username = 'john'
GROUP
BY username ) AS average
SET user.average_score = average.avg_score WHERE user.username = 'john'

[fvgb]

yes, it is the top 50 by average score. Selecting this will be easy once the average_score field is in place and updated.

[r937]

As I only want to update for the active player I modified it somewhat and it seems to function correctly.

that's weird, because you quite clearly said you wanted to update all users

your example for 1 user can be simplified

UPDATE user
INNER
  JOIN ( SELECT SUM(score)/57 AS avg_score
           FROM score
          WHERE username = 'john' ) AS average
   SET user.average_score = average.avg_score 
 WHERE user.username = 'john'

[fvgb]

Thank you for the simplified code.

Sorry for being unclear. Yes, first I thought to update the averages of all users with a cron job (which wasn't very smart) but you got me thinking and now I think it will work fine updating just the active player's average when he scores.