SQL: Total entries in past 7 days, group by day, if no entries for that day set it as 0

Question

I have a table with following columns:

id | time (SQL datetime) | url

I need to get totals for last 7 days from today.

If totals for that day are 0 - return it as 0 instead of not returning at all.

I've tried this:

SELECT 
    time, COUNT(*) AS `num` 
FROM 
    entries 
WHERE 
    time > DATE_SUB(CURDATE(), INTERVAL 7 DAY) 
GROUP BY 
    time

but it returns totals per hour, from my understanding - I somehow need to convert datetime stamp to date?

I am still learning SQL.

Answer 1

This is a very interesting question, because the answer is much more complex than the question.

In general, SQL is not designed to use loops and arbitrary length lists and generating stuff on the fly as we would in normal programming languages. It generally relies on having all data pre-existing in tables.

If all dates WERE in your existing table, no problem, just use GROUP BY. But where to get a date, which doesn't exist in the table, to display 0 count with?

Looking at SO, there have been several options discussed and listed in the past.

Basically it all boils down to 4 choices:

• use temporary or permanent tables with pre-built necessary range of dates. Compatible with all SQL servers, but somewhat yucky, plus requires remembering to keep the range table updated;
• make the list of needed dates in the programming language outside SQL and compare the returned values afterwards. Not elegant at all;
• use server-side procedures: less portable, but comparatively easy -- make a precedure which returns a list of rows with dates from the interval you specify as an argument to the procedure;
• use additional SQL server-side tweaks, such as variables.

In your case, drawing from the answers to this question: Get a list of dates between two dates , I myself would consider using MySQL variables, however, since you need to include dates, which don't exist in your data table, this probably will not work (at least I didn't find a working solution).

So, using any of the solutions in the listed question, you should create something table-like for your actual list of past 7 days dates, like this:

temporary table LASTDAYS
datetime D
-------------
2017-12-27
2017-12-26
2017-12-25
2017-12-24
2017-12-23
2017-12-22
2017-12-21

(Order is not important here)

Then you do:

SELECT DATE(LASTDAYS.D) AS D1, COUNT( YOURTABLE.id) AS num
FROM LASTDAYS LEFT OUTER JOIN YOURTABLE
ON DATE(YOURTABLE.time)=date(LASTDAYS.D)
GROUP BY D1;

(Note OUTER join)

The simplest way to create such a table would be something like:

CREATE TEMPORARY TABLE LASTDAYS (D datetime);

INSERT INTO LASTDAYS VALUES (CURRENT_TIMESTAMP);
INSERT INTO LASTDAYS VALUES (DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 1 DAY));
INSERT INTO LASTDAYS VALUES (DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 2 DAY));
INSERT INTO LASTDAYS VALUES (DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 3 DAY));
INSERT INTO LASTDAYS VALUES (DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 4 DAY));
INSERT INTO LASTDAYS VALUES (DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 5 DAY));
INSERT INTO LASTDAYS VALUES (DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 6 DAY));

However, this is is really bruteforce. Though, since you only need this for 7 rows, it could even be included in your SELECT. I am not sure, but maybe if it is enclosed in BEGIN/END transaction block, then the temporary table even won't persist between SELECTs.