Mysql count result with zero value

Question

In mysql, I got table "scores" as follow

Id  Date                Name        score
1   2011-08-24 00:00:00 sharique    10
2   2011-08-24 00:00:00 joe     11
3   2011-08-24 00:00:00 vijay       5
4   2011-08-25 00:00:00 sharique    0
5   2011-08-25 00:00:00 joe    11

Now when I am running query

SELECT date,count(id) as count FROM scores where `name` = 'vijay' group by `date`;

I am getting result as

date                 count
2011-08-24 00:00:00,  1

instead of

date                 count
2011-08-24 00:00:00,  1
2011-08-25 00:00:00,  0

how can i display result with zero count, please?

Answer 1

Here's one simple way:

SELECT s2.date, count(s1.id) as count
FROM (select distinct `date` from scores) s2 
  left join scores s1
    on  s1.`date` = s2.`date`
    and s1.`name` = 'vijay'
group by 1

This guarantees a date for everybody for every distinct date in the table