Compare and count values in mysql rows

Question

upVote   DownVote
|true   | 0
|true   | 0
|0      | true
|true   | 0
|true   | 0
|0      | true

I have columns like this above in my sql table. I want to count the total number of upVotes - Downvotes just like stackOverflow. But for some reason I am having hard time with sql syntax.

SELECT COALESCE(sum(CASE WHEN upvote THEN 1 ELSE 0 END),0) from Ratings WHERE TopicID = :topicsID

I have seen the above sql query example somewhere but not sure I am heading in the right direction.Help is appreciated

What is the issue with your query? Your on the right track, but clearly only counting upvotes. — Gordon Linoff
– Gordon Linoff, Commented Apr 9, 2018 at 15:13
IMHO the table's structure is a little bit meeh... One upvote column with a boolean value is enough. — Spoody
– Spoody, Commented Apr 9, 2018 at 15:21
What do you mean by compare the downvote, you can count all upvotes, then count all downvotes. — Spoody
– Spoody, Commented Apr 9, 2018 at 15:23

Funk Forty Niner · Accepted Answer · 2018-04-09 15:44:01Z

3

I guess you need to subtract on sum(upvote) and sum(DownVote)

SELECT sum(upvote) - sum(DownVote)
from Ratings 
WHERE TopicID = :topicsID

Fiddle: http://sqlfiddle.com/#!9/b1644c/8

I think One vote column with a boolean value is enough.

Because True can mean upvote,False can mean DownVote

CREATE TABLE Ratings
(
    Vote BOOL
);
INSERT INTO Ratings VALUES 
(TRUE),
(TRUE),
(0),
(0),
(TRUE),
(TRUE),
(0);

SELECT sum(CASE WHEN Vote THEN 1 ELSE 0 END) - 
   sum(CASE WHEN Vote THEN 0 ELSE 1 END) as vote
from Ratings 
WHERE TopicID = :topicsID

Fiddle: http://sqlfiddle.com/#!9/727c2b/3

edited Apr 9, 2018 at 15:44

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

answered Apr 9, 2018 at 15:32

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Funk Forty Niner Over a year ago

I edited this to remove the blockquotes (along with other answers of yours containing them). Those have a special use and not to be used as a personal formatting preference. This matter has been discussed on meta a few times. So, can you please stop using those?

user7533294 · Accepted Answer · 2018-04-09 15:18:39Z

0

select COUNT(*) from ratings WHERE upvote=true AND TopicID = :topicsID ?

answered Apr 9, 2018 at 15:18

user7533294

2 Comments

sach jot Over a year ago

but how would I compare and decrease the count when there is a downVote?

user7533294 Over a year ago

didnt know thats what you were aiming for but you could run the same query for false and then subtract true-false

Collectives™ on Stack Overflow

Compare and count values in mysql rows

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related