MySQL shows the number 0 for dates without records

Question

I am trying to get COUNT of all user attempts in the list of the current week (last 7 days)

This query works, but does not return 0 if the day does not exist:

SELECT COUNT(*) AS attempt_count, DATE_FORMAT(attempt_date,'%Y/%m/%d') AS attempt_date FROM users_attempts WHERE DATE_SUB(attempt_date, INTERVAL 1 DAY) > DATE_SUB(DATE(NOW()), INTERVAL 1 WEEK) GROUP BY DAY(attempt_date) DESC; 

This query returns COUNT of all attempts of the last current week per day, I got this (I have only 1 record):

 attempt_count | attempt_date 1 2014/06/19 

I want this result:

 attempt_count | attempt_date 1 2014/06/19 0 2014/06/18 0 2014/06/17 0 2014/06/16 0 2014/06/15 0 2014/06/14 0 2014/06/13 

Thank you very much

DEMO : http://sqlfiddle.com/#!2/b58bb/1/0

Answer 1

Ok from my previous answer from the MySql Single Table thread , select the last 7 days and include blank lines

Here you can make a dynamic date selection.

 select t1.attempt_date, coalesce(SUM(t1.attempt_count+t2.attempt_count), 0) AS attempt_count from ( select DATE_FORMAT(a.Date,'%Y/%m/%d') as attempt_date, '0' as attempt_count from ( select curdate() - INTERVAL (aa + (10 * ba) + (100 * ca)) DAY as Date from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c ) a where a.Date BETWEEN NOW() - INTERVAL 7 DAY AND NOW() )t1 left join ( SELECT DATE_FORMAT(attempt_date,'%Y/%m/%d') AS attempt_date, COUNT(*) AS attempt_count FROM users_attempts WHERE DATE_SUB(attempt_date, INTERVAL 1 DAY) > DATE_SUB(DATE(NOW()), INTERVAL 1 WEEK) GROUP BY DAY(attempt_date) DESC )t2 on t2.attempt_date = t1.attempt_date group by DAY(t1.attempt_date) order by t1.attempt_date desc; 

Demo

Answer 2

Create a calendar table or you can try:

 SELECT COUNT(*) AS attempt_count, DATE_FORMAT(ca_date,'%Y/%m/%d') AS attempt_date FROM (SELECT STR_TO_DATE('2014/06/19', '%Y/%m/%d') ca_date UNION ALL SELECT STR_TO_DATE('2014/06/18', '%Y/%m/%d') ca_date UNION ALL SELECT STR_TO_DATE('2014/06/17', '%Y/%m/%d') ca_date UNION ALL SELECT STR_TO_DATE('2014/06/16', '%Y/%m/%d') ca_date UNION ALL SELECT STR_TO_DATE('2014/06/15', '%Y/%m/%d') ca_date UNION ALL SELECT STR_TO_DATE('2014/06/14', '%Y/%m/%d') ca_date UNION ALL SELECT STR_TO_DATE('2014/06/13', '%Y/%m/%d') ca_date) AS calendar LEFT JOIN users_attempts ON users_attempts.attempt_date = calendar.ca_date GROUP BY calendar.ca_date ORDER BY calendar.ca_date DESC;