Search code examples
mysqlgroup-bymonthcalendar

Mysql query for group by month

I am looking to do something like get all rows from table 

where date >='2012-05-05' and date<='2012-07-20'

I want MySQL to return "group by" rows mont wise incremented from 

2012-05-05 to 2012-06-05(incerement 1 month)
2012-06-06 to 2012-07-06(increment 1` month)
and remaining 2012-07-07 to 2012-07-20 (group the remaining 14 days)

how can i write my query to achieve the same?

Thank you...

Solution

  • Try this solution:

    You can GROUP BY the number of months elapsed from your parameter minimum (2012-05-05) + 1 to the date in each row via the TIMESTAMPDIFF() function:

    GROUP BY TIMESTAMPDIFF(MONTH, '2012-05-05' + INTERVAL 1 DAY, date)

    The reason why we +1 day to your minimum parameter is because:

    • 2012-05-05 to 2012-06-04 is 0 months, but...
    • 2012-05-05 to 2012-06-05 is 1 month

    ^ Because of that, the row(s) on 2012-06-05 would be grouped separately from dates that had 0 months elapsed when we actually want it grouped WITH them.

    Edit: I was fiddling around with this solution not only grouping by the month intervals, but also displaying the from and to dates of each interval.

    Check it out:

    SQLFiddle Demo