mysql - Getting difference between counts of two subqueries

Question

I'm trying to determine the score of an entry by finding the difference between the number of upvotes and downvotes it has received in MYSQL by running SELECT (SELECT COUNT(vote_id) AS vote_up FROMvotesWHERE vote='UP'),(SELECT COUNT(vote_id) AS vote_down FROMvotesWHERE vote='DOWN'),(vote_up - vote_down AS vote_score). When I try to run this though, it tells me that I do not have proper syntax. What am I doing wrong?

Also, is there a better way to write this?

And finally, what is the ideal way to find the item with the highest and lowest number of votes? Would I just ORDER BY [above query]?

Answer 1

You can do it by pulling that last clause into a (SELECT ...) block as well:

SELECT
    (SELECT COUNT(vote_id) FROM votes WHERE vote='UP') AS vote_up,
    (SELECT COUNT(vote_id) FROM votes WHERE vote='DOWN') AS vote_down,
    (SELECT vote_up - vote_down) AS vote_score
ORDER BY vote_whatever;

Note btilly's answer about having +/- 1 be the upvote / downvote representation. It makes a lot more sense in this context, and allows for smaller tables, faster comparisons, and use of the SUM() function when necessary:

SELECT SUM(vote) from votes;

Also note: You'll only get vote_up and vote_down counts using the multiple (SELECT ...) method - SUM(CASE) will only give you the total.