MySQL show count of 0 for dates with no records

Question

I'm trying to get the COUNT of all users attempts by a list of the current week (last 7 days)

This query works but doesnt return 0 if the day 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 return the COUNT of all attempts of the last current week per day, i got this (I only have 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

Many thanks

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

are those 0 with dates are there in the DB ? if not then you need to use the following technique to get the missing date in the result http://stackoverflow.com/questions/23300303/mysql-single-table-select-last-7-days-and-include-empty-rows/23301236#23301236 — Abhik Chakraborty, Jun 19 '14 at 07:43
you could left join a calendar table, with a row per day, to your query to get the appropriate result — mc110, Jun 19 '14 at 07:44
Make one. SELECT statements will not create data that is not in the database in the first place. You can do it on the client side, or maybe in a stored procedure. But if you want to have the SELECT generate those dates, you will need to have them in some table. — Frazz, Jun 19 '14 at 07:57
@Frazz you're right but i'm trying the technique of Abhik Chakraborty, Creating a temp calendar table, i think is posible. — Jonathan Edgardo, Jun 19 '14 at 08:12

score 2 · Accepted Answer · edited May 23 '17 at 12:05

Ok from my previous answer from the thread MySql Single Table, Select last 7 days and include empty rows

Here what you can do for making the date selection dynamic

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 (a.a + (10 * b.a) + (100 * c.a)) 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

Is perfect, works perfectly, thank you very much @Abhik Chakraborty — Jonathan Edgardo, Jun 19 '14 at 08:31

score 0 · Answer 2 · answered Jun 19 '14 at 08:09

0

Create a calendar table or you could 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;

answered Jun 19 '14 at 08:09

MinhD

1,790
11
14

1

This is too specific for a week. Instead you should have used a better calendar. – Ravinder Reddy Jun 19 '14 at 08:10
Reconverting to `date_format` from date was unnecessary. – Ravinder Reddy Jun 19 '14 at 08:13
Thanks for the comment. Just a try for him to know what needs to be done. `date_format` is for presentation. – MinhD Jun 19 '14 at 08:14
Nice answer but this is not a dynamic week, but I need the last 7 days from the current day – Jonathan Edgardo Jun 19 '14 at 08:16

MySQL show count of 0 for dates with no records

2 Answers2

Linked