www.amitpatil.me www.amitpatil.me - 13 days ago 5
MySQL Question

counting upvotes and downvotes

I have an SQL table like this:

id cid u_id vote user_ip date_added
--------------------------------------------------------------
67 188 13 0 127.0.0.1 1356686070
68 188 15 1 127.0.0.1 1356686765
69 291 13 1 127.0.0.1 1356688859
70 62 15 1 127.0.0.1 1356695284
71 370 23 1 127.0.0.1 1356892215
73 232 23 1 127.0.0.1 1356893261
74 152 23 1 127.0.0.1 1356893591
76 75 15 1 127.0.0.1 1356924002
77 201 15 1 127.0.0.1 1356927284


In the
vote
column,
0
represents a downvote, and
1
represents an upvote. How would I go about calculating the downvotes and upvotes for each
cid
?

The result would be something like this:

cid upvotes downvotes
188 1 1
291 1 0
291 1 0

Ray Ray
Answer
SELECT cid,
  SUM(vote) AS upvoted,
  SUM(CASE
        WHEN vote = 0
        THEN 1
        ELSE 0
      END) AS downvoted
FROM yourtablename
GROUP BY cid;
Comments