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?

It's the expected result. You only have vijay once in your dataset.. — halfdan
– halfdan, Commented Nov 21, 2011 at 12:59
it is not the expected result. He wants to show all dates including the days without having vijay — Kayser
– Kayser, Commented Nov 21, 2011 at 13:06
That’s true, I want to display result with zero count, maybe it is expected result for a given query, but my problem is to display count for each day including zero, so that I can say on which day “vijay” did not play or score anything — superzoom
– superzoom, Commented Nov 21, 2011 at 13:12

ypercubeᵀᴹ · Accepted Answer · 2011-11-21 13:52:17Z

9

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

edited Nov 21, 2011 at 13:52

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

answered Nov 21, 2011 at 13:15

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

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

3 Comments

ean5533 Over a year ago

My thoughts exactly (except typed faster :) )

ypercubeᵀᴹ Over a year ago

It's the other way around: (select distinct ...) left join scores

superzoom Over a year ago

thanks to all, it is working now... special thanks to @ypercube

ScoRpion · Accepted Answer · 2011-11-21 13:21:25Z

0

It is The Expected Result as You Are Counting the number of rows that matches your where condition.

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

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

If you want to count all the rows use don't restrict the data by using Where Clause e.g:-

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

Like wise the following query will return 3 as count..

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

answered Nov 21, 2011 at 13:21

ScoRpion

11.5k25 gold badges72 silver badges91 bronze badges

Collectives™ on Stack Overflow

Mysql count result with zero value

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related