Group by a range of X days

Question

I have a set or records and I want to count and group them by a certain range e.g. I want to count the records that were created by groups of X days

e.g. SELECT COUNT(*) FROM `table` GROUP BY /*`created` 3 days/*

Answer 1

Here is an example with dates.

create table t1(created date not null);

insert 
  into t1(created) values (date '2011-01-09')
                         ,(date '2011-01-10')
                         ,(date '2011-01-11')
                         ,(date '2011-01-12')
                         ,(date '2011-01-13')
                         ,(date '2011-01-14')
                         ,(date '2011-01-15')
                         ,(date '2011-01-16')
                         ,(date '2011-01-17')
                         ,(date '2011-01-18')
                         ,(date '2011-01-19')
                         ,(date '2011-01-20');

select floor(datediff(now(), created) / 3) * 3 as days_ago
      ,min(created)
      ,max(created)
      ,count(*)
  from t1
 group 
    by floor(datediff(now(), created) / 3);

+----------+--------------+--------------+----------+
| days_ago | min(created) | max(created) | count(*) |
+----------+--------------+--------------+----------+
|        0 | 2011-01-18   | 2011-01-20   |        3 |
|        3 | 2011-01-15   | 2011-01-17   |        3 |
|        6 | 2011-01-12   | 2011-01-14   |        3 |
|        9 | 2011-01-09   | 2011-01-11   |        3 |
+----------+--------------+--------------+----------+
4 rows in set (0.00 sec)

Answer 2

You can do something like
SELECT COUNT(*) FROM table GROUP BY FLOOR(created / 3)

... I think.

Although if created is a date field, you'll have to do a little more jiggering to get it into a number value for this to work.