Getting non-existant month/week/year when using MySql group by clause with month/week/year date functions.

Question

I am trying to implement a query where I am using aggregates to sum certain values and then group by mysql date functions (month | year | week). Those group by clauses don't return non-existant months OR year OR week respectively for obvious reasons. I was wondering if there is a way to get them?

Answer 1

You need to create a dummy table:

CREATE TABLE t_dummy (num int PRIMARY KEY);

, fill it and use it in the OUTER JOIN:

SELECT  @start + INTERVAL num DAY, SUM(mytable.value)
FROM    t_dummy
LEFT JOIN
        mytable
ON      mytable.datetime >= @start + INTERVAL num DAY
        AND mytable.datetime < @start + INTERVAL num + 1 DAY
WHERE   t_dummy.num BETWEEN @start and @start + @days

You need as many consequent NUM's in the dummy table as many days you want to cover by your report.

Unfortunately, MySQL does not have a built-in row generator.

Answer 2

Does the solution have to be entirely in MySQL? If it's a query for a web app, then I would code the web app to look for the missing rows and to insert them in the output data.