CODEWITHSUNDEEP
sqlmysql
Patrick Desjardins
Patrick Desjardins

Reputation: 140823

SQL query with 2 grouping

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?

Upvotes: 0

Views: 92

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146499

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

Upvotes: 1

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171421

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

Upvotes: 1

Related Questions