Lomse Lomse - 8 days ago 8
MySQL Question

Is that possible to group result on 3 months period basis?

I am trying to group results by 3 months period starting by the current month as shown below:

row1 15 -- This should contain November, September and October
row2 25 -- This should contain August, July and June
row3 5 -- This should contain May, April and March
row4 2 -- This is should contain February and Janvier


I have no idea about how to accomplish this. Any help please?

So far I can group by month:

SELECT MONTH(date), MONTHNAME(date) as month, COUNT(*) FROM table_name WHERE MONTH(date) < NOW() GROUP BY MONTH(date) ORDER BY MONTH(date) DESC


Thanks

Answer

You can use PERIOD_DIFF and some maths to find the records.

-- This line creates YYYYMM representation of today; you can use PHP instead
SET @T1 = DATE_FORMAT(CURRENT_DATE, '%Y%m');

SELECT MIN(`date`) AS `Range Start`, MAX(`date`) AS `Range End`, COUNT(*) AS `Count`
FROM `table`
GROUP BY FLOOR(PERIOD_DIFF(@T1, DATE_FORMAT(`date`, '%Y%m')) / 3)
ORDER BY 1 DESC

Sample output:

Range Start  Range End   Count
-----------  ----------  -----
2013-09-01   2013-11-26  87
2013-06-01   2013-08-31  92
2013-03-01   2013-05-31  92
2012-12-01   2013-02-28  90
2012-09-01   2012-11-30  91

PERIOD_DIFF returns the number of months between periods P1 and P2 (both arguments are strings in YYYYMM format).

In the above query we calculate the month difference for each row (e.g. NOV-2013 is 0, OCT-2013 is 1, SEP-2013 is 2, AUG-2013 is 3 and so on).

The difference divided by 3 plus FLOOR yields the quarter number (NOV-2013 is 0.00 -> 0, OCT-2013 is 0.33 -> 0, SEP-2013 is 0.66 -> 0, AUG-2013 is 1.00 -> 1, and so on).

Comments