Lomse - 1 year ago 66
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 Source

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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download