How to get everytime 7 elements from database

Question

In db i have comments table and for statistics i would like to get total comments count per day (last 7 days).

I use:

select date(created_at) as day, count(*) as total_comments 
from comments 
where DATEDIFF(NOW(), created_at) <= 7 
group by day

but when there are not comments in particular day it wont return anything. How can i fill missing days?

Answer 1

You need a table for all the 7 days

select t1.day, t2. total_comments 
from (
select 1  day from dual 
union   
select 2 from dual 
union  
select 3   from dual 
union   
select 4 from dual  
union  
select 5   from dual 
union   
select 6  from dual
union 
select 7  from dual 
) t1 
left join  (
select date(created_at) as day
    , count(*) as total_comments 
from comments where DATEDIFF(NOW(), created_at) <= 7 
group by day
) t2 on t1.day = t2.day 

Answer 2

You can use a different strategy, without the need for different tables:

select 
(NOW() - INTERVAL 1 DAY) as day, (SELECT count(*) as total_comment from comments where date(created_at) = day ) as total_comments
UNION
select (NOW() - INTERVAL 2 DAY) as day, (SELECT count(*) as total_comment from comments where date(created_at) = day ) as total_comments
UNION 
select (NOW() - INTERVAL 3 DAY) as day, (SELECT count(*) as total_comment from comments where date(created_at) = day ) as total_comments
UNION
select (NOW() - INTERVAL 4 DAY) as day, (SELECT count(*) as total_comment from comments where date(created_at) = day ) as total_comments
UNION
select (NOW() - INTERVAL 5 DAY) as day, (SELECT count(*) as total_comment from comments where date(created_at) = day ) as total_comments
UNION 
select (NOW() - INTERVAL 6 DAY) as day, (SELECT count(*) as total_comment from comments where date(created_at) = day ) as total_comments
UNION 
select (NOW() - INTERVAL 7 DAY) as day, (SELECT count(*) as total_comment from comments where date(created_at) = day ) as total_comments