mysql show count as 0 for non-existing records

Question

Although I have researched similar other questions, however could not reach the solution by following those, hence posting my following question, and apologies for a long question in an attempt to make my question more clear.

The image shows my table structure

The image shows my table structure. I want to run such a query to extract the 3 information,i.e

userId, count(), Date(viewTime)

i.e the no of counts of id that a user has viewed on daily basis in a interval of last 14 days, also show count as 0 if there are no records for a user on a particular day

select userId, count(userId), Date(viewTime) from user_views
where DATE(viewTime) between DATE_SUB(DATE(NOW()), INTERVAL 90 DAY) AND now()
group by userId, date(viewTime);

By using the above query I am getting only the non-zero records, see in the following image: enter image description here

However I want to show count as 0 for those days when there are no transaction of users. How do I achieve this?

If there is no row, mysql has not chance to work on it - you need to get list of all days, either you have some good table for that or you can read this answer http://stackoverflow.com/a/2157776/1786423 — jkavalik, Jun 02 '15 at 06:53
Generate a table containing the range of dates you want, and `LEFT JOIN` that table to your query. — Tim Biegeleisen, Jun 02 '15 at 06:53
select userId, count(userId), Date(viewTime) from user_views where DATE(viewTime) between DATE_SUB(DATE(NOW()), INTERVAL 90 DAY) AND now() group by userId, date(viewTime); union all select userId, 0, null from user_views where userId not in (select userId from user_views where DATE(viewTime) between DATE_SUB(DATE(NOW()), INTERVAL 90 DAY) AND now() — chickenchilli, Jun 02 '15 at 06:54
@chickenchilli: your query still gives me the same result as my query. — Sagar Panda, Jun 02 '15 at 06:58

score 1 · Accepted Answer · edited May 23 '17 at 10:26

You need to generate the dates dynamically for this and then use left join. Also note that since you are displaying the user_id it might be needed a cross join of distinct user_id with the dynamically generated dates.

From my previous answers related to showing missing dates MySql Single Table, Select last 7 days and include empty rows

Here is one for your case

select 
t1.user_id,
coalesce(t2.cnt,0) as cnt,
t1.view_date
from
(
  select DATE_FORMAT(a.Date,'%Y-%m-%d') as view_date,
  x.user_id
  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,(select distinct user_id from user_views)x
  where a.Date between DATE_SUB(DATE(NOW()), INTERVAL 90 DAY) AND now()
)t1
left join
(
  select user_id, count(user_id) as cnt, Date(view_time) as view_time from user_views
  where DATE(view_time) between DATE_SUB(DATE(NOW()), INTERVAL 90 DAY) AND now()
  group by user_id, date(view_time)
)t2
on t2.view_time = t1.view_date
and t1.user_id = t2.user_id
order by t1.view_date,t1.user_id

http://sqlfiddle.com/#!2/4136e/5

mysql show count as 0 for non-existing records

1 Answers1