nickles nickles - 1 year ago 60
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

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

You can do it with

SELECT some_id
  , SUM(
        WHEN vote = 'UP'
        THEN 1
        WHEN vote = 'DOWN'
        THEN -1
        ELSE 0
    ) 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