GROUP BY continuous months in different years

Question

I have one table (but at many locations):

DATE         STUFF
-------------------
2011-12-01   DATA
2011-12-02   DATA
2011-12-03   DATA
...
2011-12-31   DATA
2012-01-01   DATA
2012-01-02   DATA

My table covers multiple years from 2005 to 2012. I want to get AGGREGATE Function values, i.e., SUM/AVG/MAX/MIN, for each month within each year. Easy:

GROUP BY DATE_FORMAT(DATE, '%Y'), DATE_FORMAT(DATE, '%m')

I want go do the same for 3-month time periods within those years... this works for all but one:

GROUP BY DATE_FORMAT(DATE, '%Y'), DATE_FORMAT(DATE, '%m') IN (12, 01, 02)

My other time periods work, because they are in the same year (03, 04, 05), (06, 07, 08), and (09, 10, 11)... but the GROUP BY above is grouping December of 2012 with January/February of 2012. My time period has to be December 2011 and January/February 2012, December 2010 and January/February 2011... ...

I want to keep this generic, so I don't have to update with date spans, in order to put the code in a stored procedure for multiple locations.

I've tried to join the table to it self by shifting the year ahead by one if MONTH 12. This yielded undesirable results.

Answer 1

 GROUP BY floor((month(DATE) + year(DATE) * 12 -1 + %month_shift%) / %month_group_period%)

where %month_group_period% is three (3) in your example
and %month_shift% is one (1) to obtain december, january, february together, and so on

EDIT: this works for 5 month period too (if you want)

Answer 2

I'm assuming a little bit here about what you really want to do with DATE_FORMAT(DATE, '%m') IN (12, 01, 02), but:

SELECT IF(DATE_FORMAT(DATE, '%m') = 12, DATE_FORMAT(DATE, '%Y') + 1, DATE_FORMAT(DATE, '%Y')) AS yr, 
    CASE DATE_FORMAT(DATE, '%m')
        WHEN 12 THEN 1
        WHEN 1 THEN 1
        WHEN 2 THEN 1
        WHEN 3 THEN 2
        WHEN 4 THEN 2
        WHEN 5 THEN 2
        WHEN 6 THEN 3
        WHEN 7 THEN 3
        WHEN 8 THEN 3
        WHEN 9 THEN 4
        WHEN 10 THEN 4
        WHEN 11 THEN 4
    END AS qtr
FROM ...
GROUP BY IF(DATE_FORMAT(DATE, '%m') = 12, DATE_FORMAT(DATE, '%Y') + 1, DATE_FORMAT(DATE, '%Y')), 
    CASE DATE_FORMAT(DATE, '%m')
        WHEN 12 THEN 1
        WHEN 1 THEN 1
        WHEN 2 THEN 1
        WHEN 3 THEN 2
        WHEN 4 THEN 2
        WHEN 5 THEN 2
        WHEN 6 THEN 3
        WHEN 7 THEN 3
        WHEN 8 THEN 3
        WHEN 9 THEN 4
        WHEN 10 THEN 4
        WHEN 11 THEN 4
    END