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:
$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());
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.
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
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:
SELECT *, points/played AS average_points FROM nf_users WHERE played > 0 ORDER BY average_points DESC LIMIT 100