How to add value for every date in MySQL query

Question

I have following sql-query:

    SELECT DATE(time), ROUND(AVG(out_temp),2)
    FROM data_table
    WHERE id= 1 AND time BETWEEN '2012-08-18' AND '2012-08-30'
    GROUP BY DATE(time)
    ORDER BY time ASC

This returns:

    date         avg_temp
    2012-08-18   11.41
    2012-08-19   5.90
    2012-08-28   11.22
    2012-08-29   10.07

Everything works well so far... but I would like to add missing dates with constant value like this:

    date         avg_temp
    2012-08-18   11.41
    2012-08-19   5.90
    2012-08-20   <value>
    ...          ...
    2012-08-27   <value>
    2012-08-28   11.22
    2012-08-29   10.07

How should I modify my query? Could somebody help me with this problem? I read some posts about creating a separate calendar table with prefilled date values, but I still didnt get it to work.

Answer 1

If your data table actually has data on every date, you can do this:

SELECT thedate, coalesce(ROUND(AVG(out_temp),2), <value>)
FROM (select distinct date(time) as thedate
      from data_table
     ) dates left outer join
     data_table dt
     on date(time) = thedates.date
WHERE id= 1 AND thedate BETWEEN '2012-08-18' AND '2012-08-30'
GROUP BY thedate
ORDER BY time ASC

What you need is a driving table to generate the dates that you need. You can then left join to this table, to get the summaries you want. The COALESCE function lets you put in your default value.