How to fetch users by rating with mysql query? (Top Rated)

Question

There is a query I've just made

    SELECT * 
      FROM users 
INNER JOIN ratings ON ratings.rateable=users.id 
  ORDER BY SUM(ratings.rating)/COUNT(ratings.rating)

But, it doesn't work, I just get one person in result, although there are 3 people in ratings table! I'm using php 5.

I think sum(), count() doesn't work at all! Please, help!! Cause I can't understand how to build TOP RATED system.

Answer 1

It's hard to tell because I can't see all of your stuff....but my guess is in the ' ON ratings.rateable=users.id ' part of your SQL. Are you storing the users.id field value in the rateable column in your ratings table?

Answer 2

This is just a hunch, but ratings.rateable smells like a bit to me. Any chance the one result has users.id 1?

Is ratings.rateable really the foreign key to users.id?