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

by

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!

More Related Contents:

  1. MySQL Query GROUP BY day / month / year
  2. Mysql: Select all data between two dates
  3. MySQL cumulative sum grouped by date
  4. Error related to only_full_group_by when executing a query in MySql
  5. Get a list of dates between two dates
  6. How to populate a table with a range of dates?
  7. MySQL order by before group by
  8. Grouping into interval of 5 minutes within a time range
  9. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  10. How to group by week in MySQL?
  11. Difference between two dates in MySQL
  12. How do I select between the 1st day of the current month and current day in MySQL?
  13. Group mysql query by 15 min intervals
  14. mysql: get record count between two date-time
  15. Is it possible to GROUP BY multiple columns using MySQL?
  16. How to get first day of every corresponding month in mysql?
  17. Using Count to find the number of occurrences
  18. ORDER BY date and time BEFORE GROUP BY name in mysql
  19. GROUP BY – do not group NULL
  20. Compare only day and month with date field in mysql
  21. MySQL – select data from database between two dates
  22. How to select only date from a DATETIME field in MySQL?
  23. Insert/ Update random date in MySQL
  24. MySQL SELECT most frequent by group
  25. How to compare timestamp dates with date-only parameter in MySQL?
  26. sql group by versus distinct
  27. Select only last value using group by at mysql
  28. What’s mysql’s “BETWEEN” performance over..?
  29. group by range in mysql
  30. Working with INTERVAL and CURDATE in MySQL

Leave a Comment