Mysql, ordering results by the highest score (inner query)

Hi,

I have two tables in a database;

players
- id (int)
- name (varchar)

player_votes
- id (int)
- player_id (int)
- up_vote (tinyint)

The players table contains a list of football players. In the application users can up vote or downvote a player, each up or down vote results in an entry in the player_votes table, and the up_vote field is either 1 or 0.

What I'm trying to do is a return a list of 10 players that have the highest score. Score being the total number of up_votes of a player, divided by the total number of votes.

I've come up with the following query which I think might have done it, but I'm getting an error "check the manual that corresponds to your MySQL server version for the right syntax to use near '/total_votes AS score ORDER BY score ASC' at line 5".

SELECT p.*
          FROM players AS p,
            (SELECT count(pv1.id) FROM player_votes AS pv1 WHERE player_id = p.id) AS total_votes,
            (SELECT sum(pv1.up_vote) FROM player_votes AS pv2 WHERE player_id = p.id) AS up_votes,
            up_votes/total_votes AS score
          ORDER BY score ASC

Open in new window


I suspect I've probably got this query all wrong, and maybe there's even a much easier was of doing the same thing?
SheppardDigitalAsked:
Who is Participating?
 
Mike EghtebasDatabase and Application DeveloperCommented:
SELECT p.Id
              , p.Name
              , count(pv1.id)  AS total_votes
              , sum(pv1.up_vote) / count(pv1.id)  AS score
FROM players AS p inner join player_votes AS pv1
            ON pv1.player_id = p.id
Group By Id, Name
Order By sum(pv1.up_vote) / count(pv1.id)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here