Records count per day, including 0 values

Question

I'm trying to get a query that will show number of visits per day for the last 7 days. Query that I come up with works but it has limitation I do not know how to get rid of.

Imagine, it is August 4th, 2019. Our table visits keeps timestamps of users visits to a website:

ID | timestamp
1 | 2019-08-03
2 | 2019-08-03
3 | 2019-08-02
4 | 2019-07-31
5 | 2019-07-31
6 | 2019-07-31
7 | 2019-07-31
8 | 2019-07-30
9 | 2019-07-30
10 | 2019-07-28

Objective: get number of visits to a website per day for the last 7 days. So the result should be something like:

DATE | NumberOfVisitis
2018-08-04 | 0
2018-08-03 | 2
2018-08-02 | 1
2018-08-01 | 0
2018-07-31 | 4
2018-07-30 | 1
2018-07-29 | 0

My query includes only dates registered in DB (it excludes days with no visits). This makes sense as query is data dependent, instead of calendar.

SELECT DATE_FORMAT(`timestamp`, "%Y%m/%d") AS Date, COUNT(`id`) AS
NumberOfVisitis FROM `visits` WHERE `timestamp` >= DATE_ADD(NOW(),
INTERVAL -7 DAY) GROUP BY DAY(`timestamp`) ORDER BY `timestamp` DESC

Can you please let me know how can I modify my query to include days with no visits in the query result?

See this answer. The question is for postgresql but should work for mysql. https://stackoverflow.com/questions/14793057/how-to-include-zero-0-results-in-count-aggregate — Subir Kumar Sao, Aug 04 '19 at 06:40
Consider handling issues of data display (including missing data) in application code — Strawberry, Aug 04 '19 at 08:37

score 1 · Accepted Answer · answered Aug 04 '19 at 07:09

MySQL lacks anything like Postgres's generate_series so we have to fake it.

Simplest thing to do is to make a table with a bunch of numbers in it. This will be useful for generating lots of things.

create table numbers ( number serial );
insert into numbers () values (), (), (), (), (), (), ();

From that we can generate a list of the last 7 days.

select date_sub(date(now()), interval number-1 day) as date
from numbers
order by number
limit 7

Then using that as a CTE (or a subquery) we left join it with visits. A left join means all dates will be present.

with dates as (
    select date_sub(date(now()), interval number-1 day) as date
    from numbers
    order by number
    limit 7
)
select date, coalesce(sum(id), 0)
from dates
left join visits on date = timestamp
group by date
order by date

Thx @Schwern. That is brilliant! Got it. Will adopt the concept and make it fly. — Kuba, Aug 04 '19 at 08:21

Records count per day, including 0 values

1 Answers1