SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting avg points from each user?

    I'm trying to figure out how to get avg(points) from each user in a table and then order them by the user with the highest average first...

    here is what I have tried but with no luck:
    Code:
    $sql = "SELECT *, AVG(points) points FROM nf_users WHERE played > 0 GROUP BY id ORDER BY points DESC LIMIT 100";
    $qry = mysql_query($sql) or die(mysql_error());
    Any help plaese :-)

  2. #2
    padawan silver trophybronze trophy markbrown4's Avatar
    Join Date
    Jul 2006
    Location
    Victoria, Australia
    Posts
    4,107
    Mentioned
    28 Post(s)
    Tagged
    2 Thread(s)
    Hi,

    I'm not 100% sure on the MySQL syntax but it sounds very similar to a question I asked recently which may help you.
    http://www.sitepoint.com/forums/show...ER-in-Postgres

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi jmansu,

    Without seeing your table structure, it's hard to say exactly what's going on. Could you do a "SHOW CREATE TABLE nf_users" and preferably give us some test data?

    Usually, I would expect that the id would be the primary key and therefore unique, so the GROUP BY would have no effect. I would expect that you'll see one row for each user and the points value will be exactly the same as the user's points would be without the use of the AVG(), since your group would have only one record in it (therefore points/1 = points).

    If I'm misunderstanding and the nf_users table doesn't contain the actual users themselves, but simply the scores then you just need to GROUP BY a user identifier, ie a field that is unique to a user record only (usually the user record's primary key). Without knowing your table structure it's impossible to say for sure, but I'd have expected something like user_id, rather than simply id if that was the case.

  4. #4
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is my table structure:
    ID
    username
    played
    points


    example on a tablerow:
    1,jmansa,23,435

    Hope this helps :-)

  5. #5
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Will your username appear more than once in this table? If so then change the GROUP BY from id to username.

    If you store the users elsewhere, I would recommend that you don't store the username here, but rather the ID of the user from the user table. For one, it's more efficient, both in storage requirements and in query time, and for two it will prevent you getting your data mixed up if someone changes their username. This is what we call a "Foreign Key". I won't overcomplicate it from there for now (not necessary).

    Also, I would ensure that the username field is indexed

    By the way, actually running "SHOW CREATE TABLE nf_users" as a query on the database will return the exact table structure, indexes, engine and all, so will help people diagnose your problem. We can easily create the exact same table ourselves, put some test data in and test the queries. It's very helpful

  6. #6
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Antnee View Post
    Will your username appear more than once in this table? If so then change the GROUP BY from id to username.
    A user only have 1 record in the table so I guess that GROUP BY is useless!

    Still not sure how to get the AVG points from the tablerow though...

  7. #7
    padawan silver trophybronze trophy markbrown4's Avatar
    Join Date
    Jul 2006
    Location
    Victoria, Australia
    Posts
    4,107
    Mentioned
    28 Post(s)
    Tagged
    2 Thread(s)
    If a user only has one score why are you trying to calculate an average

  8. #8
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by markbrown4 View Post
    If a user only has one score why are you trying to calculate an average
    I need to divide the points with played to get average, but I need to do so within the query and then order average points!

  9. #9
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hang on, hang on! Do you just want to say "this is how many games, this is how many points, what's the average" on a per-row basis? Because that's just maths:

    Code mysql:
    SELECT *, points/played AS average_points FROM nf_users WHERE played > 0 ORDER BY average_points DESC LIMIT 100

  10. #10
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Antnee View Post
    Hang on, hang on! Do you just want to say "this is how many games, this is how many points, what's the average" on a per-row basis? Because that's just maths:

    Code mysql:
    SELECT *, points/played AS average_points FROM nf_users WHERE played > 0 ORDER BY average_points DESC LIMIT 100
    Oh yes... I didn't think it was that easy... Thanks ;-)

  11. #11
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Often is Apologies for not realising what you were trying to achieve sooner

  12. #12
    padawan silver trophybronze trophy markbrown4's Avatar
    Join Date
    Jul 2006
    Location
    Victoria, Australia
    Posts
    4,107
    Mentioned
    28 Post(s)
    Tagged
    2 Thread(s)
    That was an important piece of information you left out

    It's best to have a row per play, that way gives you a lot more flexibility like performing min / max / averages, query performance over time etc..

  13. #13
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I thought "played" was the user that you had played against, not how many games you had played. Then the penny dropped


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
  •