Returning 0 when there are no entries in SQL

Question

I'm trying to fill a chart, however, when there is no entry, nothing will be printed. It should return a "0" when nothing exists.

I've tried to use IFNULL on my COUNT, but that does not make any difference.

SQL:

SELECT DATE_FORMAT(date(created_at), "%d. %b") AS date, IFNULL(count(*), 0) as count 
FROM users
WHERE created_at BETWEEN NOW() - INTERVAL 14 DAY AND NOW()
Group by date
ORDER BY date(created_at) ASC

Answer 1

I understand that you want to fill the missing dates in the range.

One option is to first generate the series of dates for the whole period, then bring the table with a left join, and aggregate. Again, one option uses a recursive query, available in MySQL 8.0:

with recursive dates as (
    select current_date - interval 14 day dt
    union all
    select dt + interval 1 day from dates where dt < current_date
)
select date_format(d.dt, '%d. %b'), count(u.created_at) cnt
from dates d
left join users u 
    on  u.created_at >= d.dt
    and u.created_at <  d.dt + interval 1 day
group by d.dt, date_format(d.dt, '%d. %b')
order by d.dt