Select all months within given date span, including the ones with 0 values

Question

Hi,

I'm trying to write a MySQL query to get an average value per month, for all months between to given dates. My idea is this:

Query, something like

SELECT AVG(value1) as avg_value_1, 
AVG(value2) as avg_value_2, 
MONTH(save_date) as month, 
YEAR(save_date) as year
FROM myTable
WHERE save_date BETWEEN '2009-01-01' AND '2009-07-01'
GROUP BY YEAR(save_date), MONTH(save_date)

avg_value_1 | avg_value_2 | month | year
     5      |      4      |   1   | 2009
     2      |      1      |   2   | 2009
     7      |      5      |   3   | 2009
     0      |      0      |   4   | 2009 <---
     6      |      5      |   5   | 2009
     3      |      6      |   6   | 2009

You see, no values were entered during April 2009, yet i want it to show up as a 0, 0 value in output. Any ideas on how to achieve this? Can it be done within MySQL?

Answer 1

The easiest way probably is to create a date table containing months and years and union this with your final result.

Answer 2

I agree with Lieven's answer create a table containing all the months you could ever require, and use that to "LEFT JOIN" to your results table. Remember, this is a really tiny table, only 365(ish) rows per year of data you have... And you can easily write some code to populate this table initially

We do this here, and it gives lots of benefits, for example, imagine a monthly data table with the following fields (and any others you can think of!) fully populated for all the months in a given range;

• Date (E.g. 2009-04-01)
• Day (E.g. 1)
• Day of Week (E.g. Wednesday)
• Month (E.g. 4)
• Year (E.g. 2009)
• Financial Year (E.g. 2009/10)
• Financial Quarter (E.g. 2009Q1)
• Calendar Quarter (E.g. 2009Q2)

Then combining this with your query above, as follows;

SELECT `DT`.`myYear`, `DT`.`myMonth`, 
           AVG(`myTable`.`value1`) as avg_value_1, 
           AVG(`myTable`.`value2`) as avg_value_2

FROM `dateTable` as DT
LEFT JOIN `myTable`
    ON `dateTable`.`myDate` = `myTable`.`save_date`

WHERE `dateTable`.`myDate` BETWEEN '2009-01-01' AND '2009-07-01'

GROUP BY `DT`.`myYear`, `DT`.`myMonth`

There may be some errors in my SQL code as I haven't been able to create the test tables, but hopefully you'll get the principal and alter to suit your needs!

Using this, you can change your "GROUP BY" clause to whatever you have in the "dateTable" table, which can allow you to easily report by Financial Quarter, Month, Day, Day of Week, etc.

Hope that helps!