CODEWITHSUNDEEP
mysqlsql
Norman
Norman

Reputation: 6365

How to avoid the -1 in this case

This is my table votes, where voteType 1 = a negative vote, voteType 0 = a positive vote.

"id"    "votedElm"  "voteType"  "voteProcessed" "country"
"3"     "6"         "1"         "0"             "US"//1-1=0

"4"     "8"         "0"         "0"             "US"//2+0-1=1
"9"     "8"         "1"         "0"             "US"

"5"     "9"         "0"         "0"             "US"//2+0-1=1
"10"    "9"         "1"         "0"             "US"

and this my table likes

"id"    "type"  "parent"    "country"   "votes"
    6       10      3           US          1
    8       10      7           US          2
    9       10      7           US          2

In the above, I run the following sql to count the votes in votes and then add or subtract them from the likes.

SELECT
  votedElm,
  SUM(CASE WHEN voteType = 0 THEN 1 ELSE -1 END) AS Totalcount 
FROM votes 
WHERE country = 'US'
GROUP BY votedElm;

However, for id 6 in votes, when using the above query, the result is -1. So, 1 (in likes) minus -1 = 2. The result I wanted was 0. Also, for 8 and 9 in the votes table, the result should have been 1 (2-1+0=1). Here it show as 0.

Can you see where I'm going wrong and how I can get this right?

Upvotes: 0

Views: 58

Answers (1)

Amit Singh
Amit Singh

Reputation: 8119

I think you don't want to show the votes in negative.So.First count the number of vote than minus the number of unlike..... Try like this...

 Select voteby,(Count(votetype)-Sum(Case when votetype=1 then 1 else 0 end)) as b
    from t group by voteby

Sql Fiddle Demo

Upvotes: 3

Related Questions