MySQL Group By Dates Between

Question

Is there a way to group records that fall between two dates?

For example, my table has records that look like this:

rid     stamp                   uid
25      2005-07-05 14:10:29     25
1175    2005-08-12 15:47:35     29
290     2005-11-22 16:38:53     42
30      2005-12-01 10:48:12     47
30      2006-01-02 17:34:28     52
30      2006-02-06 22:11:35     57
30      2006-04-17 15:10:19     59
1195    2006-05-08 21:55:56     62
100     2006-06-30 15:51:04     94
45      2006-07-03 21:14:37     24

I'm trying to write a query that will return a count of records between the months of February - August and between September - January by year, so that what I get back is:

July 2005 - January 2006:      3
February 2006 - August 2006:   5

Answer 1

You can group on -almost- anything you want to. If you can get something in a column to show what you want, you can group on it. So for just two periods you could simply group on an if:

SELECT * FROM table
GROUP BY if(stamp between "2005-07-01" and "2006-02-01", 0, 1)

If you need the periods to range over longer times (multiple years), you could use period_diff to distinguish:

SELECT * FROM table
GROUP BY floor(period_diff( DATE_FORMAT(stamp, "%Y%m"), "200507") / 6)

It will give you the number of 6-month blocks passed between your date and the start-period in year-month format.

Answer 2

If there were only one group then you could use the "group by with having clause". I don't think there is syntax to specify multiple different groupings in one statement and separate them though. Here is the simplest alternative solution I can think of that will work:

select "July 2005 - January 2006" AS "Date", count(date) as "results" 
from MYTABLE 
where date >= '2005-07-01' AND date <= '2006-01-31'

union

select "February 2006 - August 2006" AS "Date", count(date) as "results" 
from MYTABLE 
where date >= '2006-02-01' AND date <= '2006-08-31';

Answer 3

In slight combination of the others... you can expand the case structure for however range specific you want. The group by respects the "ordinal" column 1 position so you don't have to copy the same case condition in the group by clause. Additionally, I threw in the Order by to have same context of the case since it would otherwise order the results alphabetically. You can order by whatever you wanted... even in reverse if you wanted most current date activity listed first.

SELECT
      case when stamp between "2005-07-01" and "2006-02-01"
              then "July 2005 - January 2006   "
           when stamp between "2006-02-01" and "2006-08-01"
              then "February 2006 - August 2006"
           else    "After August 2006          "
      end as GroupColumn,
      count(*) as TotalPerGroup
   from  
      table
   where 
      date >= '2005-07-01'
   group by 
      1
   order by 
      case when stamp between "2005-07-01" and "2006-02-01"
              then 1
           when stamp between "2006-02-01" and "2006-08-01"
              then 2
           else    3
      end