0

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?

2 Answers2

0

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.

Community
  • 1
  • 1
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
0

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.

staticsan
  • 29,935
  • 4
  • 60
  • 73