CODEWITHSUNDEEP
mysqlsql
frosty
frosty

Reputation: 2851

COUNT null values when using GROUP BY

I'm counting the number of votes given to an item, and there's only two possible values:

vote = 1 and vote = 2.

SELECT COUNT(*) AS count 
FROM match_votes
WHERE match_id = :match_id 
GROUP BY vote

However if there has been no votes for 1 but votes for 2 then it will only return 1 row since it doesn't count null values. I want to return count for vote = 1 and vote = 2, even if there's no votes for the item.

Also, I want to ensure that the count for vote = 1 is always the first row.

Upvotes: 0

Views: 1048

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You need a left join:

select v.vote, count(mv.vote)
from (select 1 as vote union all select 2) v left join
     match_votes mv
     on mv.vote = v.vote
group by v.vote;

Or, put the results in two columns:

select sum(mv.vote = 1) as vote_1, sum(mv.vote = 2) as vote_2
from match_votes mv;

Upvotes: 3

Related Questions