Mysql result with zero value

Question

In mysql, I got the scores table as follows

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 run the request

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

I get the result as

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

instead

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

how can i display the result with a zero number please?

Answer 1

Here is 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 each for each individual date in the table.

Answer 2

This is the expected result when counting the number of rows matching your condition.

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

It selects only one record, so the result of count is 1.

If you want to assume that all the rows used do not limit the data, use Where Clause for example: -

 SELECT date,count(id) as count FROM scores group by `date`; 

How wise, the next query will return 3 as the score.

 SELECT date,count(id) as count FROM scores where id in (1,2,3) group by `date`;