SQL query with 2 grouping

Question

This is a query that is executed without problem with MySql 5.0.51:

SELECT cc_split.idSplit,
       count( voteup.vote),
       count( votedown.vote)
FROM cc_split
LEFT JOIN cc_split_usager AS voteup ON voteup.idSplit = cc_split.idSplit
      AND voteup.vote >0
LEFT JOIN cc_split_usager AS votedown ON votedown.idSplit = cc_split.idSplit
      AND votedown.vote <0
WHERE cc_split.isArchived = false
GROUP BY cc_split.idSplit
LIMIT 0, 30

The problem is with the COUNT that doesn't display what I would like to have. Has you can see, it takes the cc_ split_ usager and should count the number of vote that is positive and on the other hand count the amount of vote that is negative. The code above display the number 2 upvote and 2 downvote when in fact it should be 2 upvote and 1 downvote. What is the trick to have in a single SQL query the number of upvote and downvote.

The tables look like:

cc_split:
-idSplit
-...
cc_split_usager:
-idSplit
-vote (can be +1 or -1)
-...

Any hint for me?

Answer 1

Try:

SELECT s.idSplit, 
    count(case when v.vote > 0 then 1 else null end) as VoteUp, 
    count(case when v.vote < 0 then 1 else null end) as VoteDown 
FROM cc_split s
LEFT JOIN cc_split_usager AS v ON v.idSplit = s.idSplit
WHERE s.isArchived = false
GROUP BY s.idSplit
LIMIT 0, 30

Answer 2

try this:

SELECT s.idSplit, 
    Sum( Case When vote > 0 Then 1 Else 0 End) UpVotes,
    Sum( Case When vote < 0 Then 1 Else 0 End) DownVotes
FROM cc_split s       
   LEFT JOIN cc_split_usager v 
      ON v.idSplit = s.idSplit        
WHERE s.isArchived = false        
GROUP BY s.idSplit        
LIMIT 0 , 30