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
```

# Best How To :

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()`

.