Group mysql results by "every 30 days"

Question

I have a query:

SELECT 
  COUNT(id) as amount,
  DATEDIFF(expire, buydate) as days
FROM `vw8mv_orders`
GROUP BY MONTH(expire)

The result is:

    amount  days
    1       22
    1       30
    1       105
    1       161

I'd like to see these results in a group (every 30 days). If days value is between 1 and 30 days, then put this in 30days group, if bet 31-60, put to 60days group, etc.

For example:

    amount  time
      2     30 days
      0     60 days
      1     90 days

Answer 1

You will need to create a calculated column to group by. There are several approaches you could use for the calculation, but a good option might be integer division using the DIV operator:

SELECT
    COUNT(id) as amount,
    (((datediff(expire, buydate) DIV 30) + 1) * 30) as timegroup
FROM
    table
GROUP BY timegroup;

The reason I like this approach, rather than using for example some fancy arithmetic with ROUND(), is that it's a little more clear what you're trying to do. datediff(expire, buydate) DIV 30 says, take the difference of these dates, and tell me "how many 30s" are in that number.

That's all you need for your grouping; the rest is there to make the column display the way you want it, as 30, 60, 90, ... instead of as 0, 1, 2, ....

Another option, if you're not comfortable with integer division, would be the CEILING function:

SELECT
    COUNT(id) as amount,
    30 * CEILING(datediff(expire, buydate) / 30) as timegroup
FROM
    table
GROUP BY timegroup;

Mathematically speaking, CEILING(x / N) is equivalent to ((x DIV N) + 1), but it's a little less busy with CEILING().

Answer 2

You can do a subselect over the result returned from your query,below is the example query

SELECT COUNT(`amount`) as amount,
 CONCAT(ROUND(`days` / 30) * 30, ' Days')
 as `time`
 FROM `t`
 GROUP BY `time`
ORDER BY ROUND(`days` / 30)

Demo

For your query you can do so

SELECT COUNT(`amount`) as amount,
 CONCAT(ROUND(`days` / 30) * 30, ' Days')
 as `time`
FROM(
SELECT COUNT(id) as amount,
 datediff(expire, buydate) as days 
FROM `vw8mv_orders` 
GROUP BY MONTH(expire)
) t
 GROUP BY `time`
ORDER BY ROUND(`days` / 30)