I have two tables in a database;
- id (int)
- name (varchar)
- 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".
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
I suspect I've probably got this query all wrong, and maybe there's even a much easier was of doing the same thing?