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