StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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</p> <p>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;</p> <ul> <li>Date (E.g. 2009-04-01)</li> <li>Day (E.g. 1)</li> <li>Day of Week (E.g. Wednesday)</li> <li>Month (E.g. 4)</li> <li>Year (E.g. 2009)</li> <li>Financial Year (E.g. 2009/10)</li> <li>Financial Quarter (E.g. 2009Q1)</li> <li>Calendar Quarter (E.g. 2009Q2)</li> </ul> <p>Then combining this with your query above, as follows;</p> <pre> 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` </pre> <p>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!</p> <p>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.</p> <p>Hope that helps!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload