We help IT Professionals succeed at work.

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

SheppardDigital
on
552 Views
Last Modified: 2014-12-15
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?
Comment
Watch Question

Database and Application Developer
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.