COUNT null values when using GROUP BY

Question

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.

Gordon Linoff · Accepted Answer · 2016-03-12 13:04:19Z

3

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;

answered Mar 12, 2016 at 13:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

frosty Over a year ago

first query still didn't count the nulls I think, but the second one worked

Collectives™ on Stack Overflow

COUNT null values when using GROUP BY

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related