title: Dynamically Create/Select Rows of Dates in MySQL
body: <p>Using the following nested <code>SELECT ... UNION SELECT ...</code> query I can return rows of Month/Year for the last 6 months (including the current month)</p>
  
  <pre><code>SELECT month_year
  FROM
    (SELECT DATE_FORMAT(NOW(),'%M %Y') as month_year
     UNION
     SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),'%M %Y')
     UNION
     SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 2 MONTH),'%M %Y')
     UNION
     SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 3 MONTH),'%M %Y')
     UNION
     SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 4 MONTH),'%M %Y')
     UNION
     SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 5 MONTH),'%M %Y')
      ) a
  </code></pre>
  
  <p>results in-</p>
  
  <pre><code>month_year
  April 2015
  March 2015
  February 2015
  January 2015
  December 2014
  November 2014
  </code></pre>
  
  <p>Is it possible to recreate this query so that is created dynamically, namely not have to add/remove <code>UNION SELECT ...</code> to get more/less Month/Year rows?</p>
  
  <p>Clearly the following query won't work, but is there some way to do something like-</p>
  
  <pre><code>SELECT month_year
  FROM
    (SELECT DATE_FORMAT(`thisDate`,'%M %Y') as month_year WHERE `thisDate` BETWEEN NOW() AND DATE_SUB(NOW(), INTERVAL 5 MONTH) LIMIT 6
    ) a
  </code></pre>
  
  <p>where I only have to change the <code>INTERVAL 5</code> and <code>LIMIT 6</code> integers to change the number of rows returned.</p>
  
  <hr>
  
  <p><strong>Application</strong></p>
  
  <p>If I have a table <code>sales</code> and each row is the amount sold on that day.</p>
  
  <pre><code>date          amount
  2015-01-03    100
  2015-01-10    50
  2015-03-07    75
  2015-03-14    60
  2015-03-21    40
  ...
  2014-10-31    125
  </code></pre>
  
  <p>Now I want to get the totals per month, for the last 6 months. If I do a standard query, using a <code>LIMIT 6</code></p>
  
  <pre><code>SELECT
     DATE_FORMAT(date,'%M %Y') as month_year, SUM(amount)
  FROM
     sales
  GROUP BY
     month_year
  LIMIT 6
  </code></pre>
  
  <p>I will end up with <code>October 2014</code> as there is no rows from <code>April 2015</code> OR <code>February 2015</code>.</p>
  
  <p>So my thinking is to create a 'temporary' table of rows of date, and then <code>LEFT JOIN</code> my <code>sales</code> query -</p>
  
  <pre><code>SELECT a.month_year, b.amount
  FROM
    (SELECT DATE_FORMAT(NOW(),'%M %Y') as month_year
     UNION
     SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),'%M %Y')
     UNION
     SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 2 MONTH),'%M %Y')
     UNION
     SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 3 MONTH),'%M %Y')
     UNION
     SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 4 MONTH),'%M %Y')
     UNION
     SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 5 MONTH),'%M %Y')
    ) a
  LEFT JOIN 
    (SELECT
         DATE_FORMAT(date,'%M %Y') as month_year, SUM(amount)
     FROM
         sales
     GROUP BY
         month_year
     ) b
   ON b.month_year = a.month_year
  </code></pre>
  
  <p>But, if I want to change the months to 7,8,9 or 3,4,5 I will have to add/remove rows, where I would like to be able to just change 1 or 2 integers.</p>