MySQL GROUP BY quantity

Question

MySQL table is like this (VoteID is PK):

VoteID  VoteValue CommentID
1         -1         1
2         -1         1
3          1         1
4         -1         1
5          1         2
6          1         2
7         -1         2

I need a result:

CommentID    Plus    Minus
    1          1      3 
    2          2      1

Sum of "Pluses", Sum of "Minuses" groupped by CommentID Is it possible to get desired in one SQL expression?

score 4 · Accepted Answer · answered Jul 09 '10 at 14:29

4

SELECT 
    CommentID,
    SUM(CASE WHEN VoteValue > 0 THEN 1 ELSE 0 END) AS PLUS,
    SUM(CASE WHEN VoteValue < 0 THEN 1 ELSE 0 END) AS MINUS

FROM 
    mytable

GROUP BY 
    CommentID

answered Jul 09 '10 at 14:29

mechanical_meat

163,903
24
228
223

score 0 · Answer 2 · answered Jul 09 '10 at 14:30

0

You need a query in the lines of:

SELECT CommentID,
    SUM(IF(VoteValue > 0, 1, 0)) AS Plus,
    SUM(IF(VoteValue < 0, 1, 0)) AS Minus
FROM yourTable
GROUP BY CommentID
ORDER BY CommentID

answered Jul 09 '10 at 14:30

Anax

9,122
5
34
68

MySQL GROUP BY quantity

2 Answers2