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 with

SELECT some_id
  , SUM(
      CASE
        WHEN vote = 'UP'
        THEN 1
        WHEN vote = 'DOWN'
        THEN -1
        ELSE 0
      END
    ) as vote_score
FROM votes
GROUP BY some_id 

Note that the better approach is to have +1 or -1 stored in vote, then you can just do:

SELECT some_id, SUM(vote) as vote_score
FROM votes
GROUP BY some_id

BTW if my formatting looks odd to you, I explained it in http://bentilly.blogspot.com/2011/02/sql-formatting-style.html.

Answer 2

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.

Answer 3

Following up on btilly's answer, If you need to know the lowest and highest but do not need to know what ID has the highest/lowest:

SELECT MIN(score), MAX(score)
FROM (
    SELECT SUM(IF(vote = 'DOWN', -1, vote = 'UP')) AS score
    FROM votes
    GROUP BY ID
)

If you do need to know the ID, use the inner query (add the ID to the select) with a ORDER BY score LIMIT 1 to get the lowest and ORDER BY score DESC LIMIT 1 to get the highest. note in the case of ties, this will choose only 1 of them.