Geek asks and answers

Count () arithmetic in mysql

I have two tables:

comments (
  id        int
  comment   int
)

commentvotes (
  commentid int
  positive  bool
)

If commentovotes.positive determines whether the vote is an increase or a lower period.

Is there any way, with one query, so that I can get all upvotes and downvotes, ideally in each column? More generally, can you run two accounts with different selection criteria in the same query?

+3
source share
1 answer

You can do stupid tricks with SUM:

SELECT commentid, SUM(positive) upvotes, SUM(NOT positive) downvotes FROM commentvotes;

MySQL booleans are actually just integers, 0 or 1. Adding them effectively counts how many times the condition is true. Therefore, if you have:

commentid | positive
----------|---------
 1        |  true
 1        |  true
 1        |  false
 1        |  true
 1        |  false

SUM(positive) 1+1+0+1+0= 3. SUM(NOT positive) is 0+0+1+0+1= 2. , .

: MySQL , positive TINYINT(1) , , SUM(positive). SUM(NOT NOT positive), , , - .;)

( ), - comments upvotes downvotes. upvotes downvotes, , , MySQL .

+5

More articles:


All Articles