CODEWITHSUNDEEP
mysqldatabase
jjj
jjj

Reputation: 245

mysql query question on count

SELECT if((COUNT(vote=1)-COUNT(vote=0) > 0,1,count(groupid))
FROM sample WHERE uid = $uid GROUP BY groupid

In the if statement, I was wondering if there is any simple way to achieve this: COUNT(vote=1)-COUNT(vote=0) > 0

Upvotes: 0

Views: 81

Answers (3)

Tony318
Tony318

Reputation: 562

are you trying to do 

Select columns, 
case when (COUNT(vote=1) - COUNT(vote=0)) > 0
then 'something'
else 'something else' as foo
From Sample
Where uid=$uid
Group By groupid

I don't quite understand what you are trying to achieve. With my example, you select the columns (or just that case), when the difference is greater than 0. If it is greater show 'something' else show 'something else'

although, i don't have a way to test it on this computer, but maybe it will lead you or someone else in the right direction.

Upvotes: 0

Bohemian
Bohemian

Reputation: 424993

You want the sum of vote, except 0 counts as -1:

SELECT if(sum(if(vote, 1, -1)) > 0, 1, count(groupid))
...

Upvotes: 1

Jonathan Amend
Jonathan Amend

Reputation: 12815

Use SUM instead of COUNT:

SELECT if((SUM(vote=1)-SUM(vote=0) > 0,1,count(groupid))
FROM sample WHERE uid = $uid GROUP BY groupid

Upvotes: 3

Related Questions