Getting the difference between counting two subqueries

Question

I am trying to determine the score of a record, I find the difference between the number of upvotes and downvotes that she received in MYSQL by running SELECT (SELECT COUNT(vote_id) AS vote_up FROMvotes WHERE vote='UP'),(SELECT COUNT(vote_id) AS vote_down FROMvote WHERE vote='DOWN'),(vote_up - vote_down AS vote_score). When I try to run this, it tells me that I do not have the correct 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 votes? Am 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 best approach is to have +1 or -1 saved during the vote, then you can simply do:

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

, , http://bentilly.blogspot.com/2011/02/sql-formatting-style.html.

Answer 2

, (SELECT ...):

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;

, btilly , +/- 1 upvote/downvote. , SUM():

SELECT SUM(vote) from votes;

: vote_up vote_down, (SELECT ...) - SUM(CASE), .

Answer 3

After btilly's answer, if you need to know the lowest and highest value, but not know which identifier 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 need to know the identifier, use the internal query (add IDto the selection) with ORDER BY score LIMIT 1to get the lowest value and ORDER BY score DESC LIMIT 1to get the maximum result. note that in the case of links this will select only 1 of them.