Geek asks and answers

How in SQL to summarize (+1) and down (-1) votes using values ​​1 and 0?

I realized this problem by trying to sum upvotes AND downvotes because the downvote value is -1 rather than 0. This causes a problem when using the sum () SQL function. For instance,

vote_id   vote
  1        0
  2        1
  3        1

SELECT sum(vote) //output is 2

vote_id   vote
  1       -1
  2        1
  3        1

SELECT sum(vote) //output is 1, which is the desired output

I guess, firstly, my question is, 0, null, and 1does use even make sense? Or should I just use -1, 0, 1?

Regardless, I would be interested to know what the SQL query would look like to summarize and down votes using 0, null, and 1.

+3
source share
7 answers

I personally would use -1, 0and 1.

0, NULL 1 :

SELECT SUM(vote * 2 - 1) ...
+4

, : NULL ?

. NULL:

SQL . Null-elim, Null . Null, , .

, , 0 NULL , SUM.

+2

SUM( decode( vote,0,-1,vote ))
+1

-1 +1.

SUM( 2*(vote-0.5))

.

SELECT (SELECT COUNT(*) FROM tbl WHERE vote = 1)
      -(SELECT COUNT(*) FROM tbl WHERE vote = 0)
+1

I think you should definitely use 1, 0 and -1. Everything is simply simplified. You can also not save any space using a single-bit field, so there is no real reason to try using it.

+1
source

You can also use CASEfor something like this: I do this a lot in SQL Server and assume that it should also be correct in MySQL:

SELECT
   SUM(CASE WHEN Vote = 0 THEN -1 ELSE VOTE END) as 'Votes'
FROM
   MyTable
+1
source

select count (*) from table where vote <> 1 (for quantity -1)

select count (*) from table where vote = 1 (for quantity 1)

-1
source

More articles:


All Articles