SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sum from other table

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by fvgb View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by fvgb View Post
    ...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
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fvgb View Post
    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?
    Code SQL:
    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?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  6. #6
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'

  7. #7
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, it is the top 50 by average score. Selecting this will be easy once the average_score field is in place and updated.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by fvgb View Post
    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
    Code:
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •