Dynamically Create/Select Rows of Dates in MySQL

Question

Using the following nested SELECT ... UNION SELECT ... query I can return rows of Month/Year for the last 6 months (including the current month)

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

results in-

month_year
April 2015
March 2015
February 2015
January 2015
December 2014
November 2014

Is it possible to recreate this query so that is created dynamically, namely not have to add/remove UNION SELECT ... to get more/less Month/Year rows?

Clearly the following query won't work, but is there some way to do something like-

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

where I only have to change the INTERVAL 5 and LIMIT 6 integers to change the number of rows returned.

Application

If I have a table sales and each row is the amount sold on that day.

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

Now I want to get the totals per month, for the last 6 months. If I do a standard query, using a LIMIT 6

SELECT
   DATE_FORMAT(date,'%M %Y') as month_year, SUM(amount)
FROM
   sales
GROUP BY
   month_year
LIMIT 6

I will end up with October 2014 as there is no rows from April 2015 OR February 2015.

So my thinking is to create a 'temporary' table of rows of date, and then LEFT JOIN my sales query -

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

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.

Could you speak a bit more to your usage? I mean, it would be simple to do a group on a date field, and using MySQL to generate a bunch of months without additional data would be somewhat ridiculous, so this is a question that may be answered better with more context. — John Green, Apr 02 '15 at 21:39
@JohnGreen I have updated my question with the application of the query. — Sean, Apr 02 '15 at 22:08
Ah, so you have gaps, caused by the GROUP BY that you're trying to avoid. Are you making a view? This would be trivial with dynamic SQL. — John Green, Apr 03 '15 at 00:12

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

I believe you can use this

You can set how old is the search by changing "INTERVAL XX MONTH".

Downside is: If you end up with a month without sales, that month will not be present in the query result.

SELECT
MONTH(`date`) AS period,
SUM(ammount) AS clicks
FROM `sales`
WHERE `date` >= CURDATE() - INTERVAL 10 MONTH
GROUP BY period

Credits: Group by date range on weeks/months interval

edited May 23 '17 at 12:06

Community

1
1

answered Apr 02 '15 at 22:20

Arthur Samarcos

3,262
22
25

1

Your 'Downside' is the issue. I want that last 6 months, concurrently, even if the month returns a NULL or 0 amount. – Sean Apr 02 '15 at 22:29

Dynamically Create/Select Rows of Dates in MySQL

1 Answers1