CODEWITHSUNDEEP
mysqlsql
Stefan
Stefan

Reputation: 137

get result of non-existent dates too

I have a MySql table containing events having a DATETIME timestamp. I want to count each day's events. On some days, e.g. on Sundays, events are missing. The result should contain these days too with a count of zero.

My query is the following:

SELECT 

COUNT(1) AS mycount,
DATE_FORMAT(DATE(evaluations.timestamp),"%a, %d.%m.%Y") AS date
FROM Events 
GROUP BY DATE(timestamp)
ORDER BY DATE(timestamp) DESC

Can I modify the query without using a helper table containing all dates? A single query (no procedere, no function) would be fine.

Upvotes: 0

Views: 126

Answers (1)

1000111
1000111

Reputation: 13519

The query would somehow look like this if you don't have any calendar table:

SELECT 
 dateTable.day,
 COALESCE(t.mycount,0) AS cnt
FROM 
(
SELECT ADDDATE((SELECT MIN(DATE(timestamp)) FROM Events), INTERVAL @i:=@i+1 DAY) AS DAY
FROM (
SELECT a.a
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
JOIN (SELECT @i := -1) r1
WHERE 
@i < DATEDIFF((SELECT MAX(DATE(timestamp)) FROM Events), (SELECT MIN(DATE(timestamp)) FROM Events))
) AS dateTable
LEFT JOIN 
(
        SELECT 
        COUNT(1) AS mycount,
        DATE_FORMAT(DATE(evaluations.timestamp),"%a, %d.%m.%Y") AS date
        FROM Events 
        GROUP BY DATE(timestamp)
        ORDER BY DATE(timestamp) DESC
) AS t
ON dateTable.day = t.date
ORDER BY dateTable.day DESC;

Note:

If you think you will need this kind of query too often then you can create a table where all the dates would reside. Newer dates can be added through mysql event periodically .

Then the work is simple. Just need to make a LEFT JOIN between the calendar table and the result of your query.

Upvotes: 1

Related Questions