-1

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
GMB
  • 216,147
  • 25
  • 84
  • 135
CunnertA
  • 165
  • 2
  • 12
  • 1
    What is the resulting data and how are you generating the chart? – showdev Oct 10 '20 at 00:33
  • I'm using Larapex, a wrapper for ApexCharts. Results are fine, but when theres a new day and no registers are made yet, the chart just cuts off because it does return nothing instead of "0" – CunnertA Oct 10 '20 at 00:38
  • You might find this helpful: [Count Returning blank instead of 0](https://stackoverflow.com/questions/19221630/count-returning-blank-instead-of-0). – showdev Oct 10 '20 at 01:00
  • Consider handling issues of data display in application code – Strawberry Oct 10 '20 at 07:27

1 Answers1

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
GMB
  • 216,147
  • 25
  • 84
  • 135
  • What does "dates" do? If I use this, I get an error, that there is no column called "dates". – CunnertA Oct 10 '20 at 17:52
  • @CunnertA: `dates` is a series of dates for the last 14 days, as explained in the answer itself. But this was missing the `recursive` keyword - fixed now. – GMB Oct 10 '20 at 18:00
  • Now its not spitting out an error but nothing. I checked the syntax and it seems to be valid though. Do I need to use SELECT somewhere? Because when I add SELECT to the beginning, my IDE also syntax highlights everything, now its just green. I tried with SELECT at the beginning though, but I get an error with that. – CunnertA Oct 10 '20 at 19:13
  • I got this working now. I still used "count" instead of "cnt". Also, after ```date_format(d.dt, '%d. %b.')``` there should be "date" so it also prints the correct date. – CunnertA Oct 10 '20 at 19:46