title: Cumulative monthly reporting
body: <p>I have a MySQL table of photovoltaic electricity generation data (pvdata) from which I need to produce a monthly summary table. A simplified table is shown:</p>
  
  <p>id &nbsp; &nbsp; &nbsp; &nbsp; date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; time &nbsp; &nbsp; &nbsp; pvdata<br>
  1 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 2012-01-01 &nbsp; &nbsp;  10:00 &nbsp; &nbsp; 50<br>
  1 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 2012-01-31 &nbsp; &nbsp;  12:00 &nbsp; &nbsp; 60<br>
  1 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 2012-02-10 &nbsp; &nbsp;  13:00 &nbsp; &nbsp;&nbsp;70<br>
  2 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 2012-02-08 &nbsp; &nbsp;  10:00 &nbsp; &nbsp; 12<br>
  2 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 2012-03-20 &nbsp; &nbsp;  10:00 &nbsp; &nbsp; 17<br></p>
  
  <p>The monthly summary table needs to show the cumulative generation for all systems in the database, regardless of whether I have received data for that month, so for example month 3 below contains the total generation from id = 1 (data received in month 2). </p>
  
  <p>Also there may be more than one data point for an id in the same month, so the report must report the max(data) for the month.</p>
  
  <p>year &nbsp; &nbsp; &nbsp; month &nbsp; &nbsp; cum_data<br>
  2012 &nbsp; &nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 60<br>
  2012 &nbsp; &nbsp;&nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 82<br>
  2012 &nbsp; &nbsp;&nbsp; 3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 87<br></p>
  
  <p>I am pretty new to this, so have struggled for a while.  The best I can come up with shows the cumulative total for the month, but without including the cumulative total for ids for which there is no data in the current month:</p>
  
  <pre><code>CREATE TEMPORARY TABLE intermed_gen_report  
  SELECT year(date) AS year, month(date) AS month, id, max(pvdata) AS maxpvdata
  FROM pvdata
  GROUP BY id, year(date), month(date)
  ORDER BY year(date), month(date);
  
  SELECT year, month, SUM(maxpvdata) AS cum_data
  FROM intermed_gen_report
  GROUP BY year, month
  ORDER BY year, month;
  </code></pre>
  
  <p>Giving:  </p>
  
  <p>year &nbsp; &nbsp; &nbsp; month &nbsp; &nbsp; cum_data<br>
  2012 &nbsp; &nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 60<br>
  2012 &nbsp; &nbsp;&nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 82<br>
  2012 &nbsp; &nbsp;&nbsp; 3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 17<br></p>