Getting avg points from each user?

[jmansa]

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());

Any help plaese :-)

[markbrown4]

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

[Antnee]

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.

[jmansa]

Here is my table structure:
ID
username
played
points

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

Hope this helps :-)

[Antnee]

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

[jmansa]

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...

[markbrown4]

If a user only has one score why are you trying to calculate an average

[jmansa]

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!

[Antnee]

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

[jmansa]

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

Oh yes... I didn't think it was that easy... Thanks ;-)

[Antnee]

Often is Apologies for not realising what you were trying to achieve sooner

[markbrown4]

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..

[Antnee]

Yeah, I thought "played" was the user that you had played against, not how many games you had played. Then the penny dropped