CODEWITHSUNDEEP
mysqlsqldatejoingroup-by
Bill Newton
Bill Newton

Reputation: 45

show days with 0 records in mysql query when grouping by date

I have a table of ids and dates. the table is like this:

id | Date

5 | 2020-04-01

6 | 2020-04-06

7 | 2020-04-11

8 | 2020-04-11

I want to group by date so I used this:

select Date,count(id) from Table where Date between '2020-04-01' AND '2020-04-30' group by Date

and I get this:

Date | count(id)

2020-04-01 | 1

2020-04-06 | 1

2020-04-11 | 2

what I need is simply a filler for empty days of the month. so instead of the result I need the table to include all days with 0 records so like this:

Date | count(id)

2020-04-01 | 1

2020-04-02 | 0

2020-04-03 | 0

2020-04-04 | 0

2020-04-05 | 0

2020-04-06 | 1

... etc

is there a way in mysql to show days with 0 records?

Upvotes: 0

Views: 692

Answers (1)

GMB
GMB

Reputation: 222492

If you are running MySQL 8.0, you can use a recursive query to generate a list of days for the current month, then bring your table with a left join, and aggregate:

with recursive cte as (
    select 
        date_format(current_date, '%Y-%m-01') dt, 
        date_format(current_date, '%Y-%m-01') + interval 1 month end_dt
    union all
    select dt + interval 1 day, end_dt from cte where dt < end_dt
)
select c.dt, count(t.id)
from cte c
left join mytable t on t.date = c.dt
group by c.dt

Upvotes: 1

Related Questions