nickles nickles - 6 months ago 10
MySQL Question

Getting difference between counts of two subqueries

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 FROM
votes
WHERE vote='UP'),(SELECT COUNT(vote_id) AS vote_down FROM
votes
WHERE 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

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.