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
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?
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
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’
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'
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.