GROUP BY weeks; How to display a range?

Question

MySQL

SELECT
  DATE_FORMAT(`start_date`, '%x-%v') weeks,
  COUNT(*)
FROM `events` GROUP BY weeks;

Outputs in the format [2011-05, 34], i.e. year, week number and number of events. You need to output something like [2011-01-31 - 2011-02-06, 34].

There is an option to process this on the application side, but I would still like to leave it to SQL.

Answer 1

This option:

SELECT
  DATE_FORMAT(`start_date`, '%x-%v') weeks,
  COUNT(*), DATE_ADD(`start_date`, INTERVAL(2-DAYOFWEEK(`start_date`)) DAY) first_date,
    DATE_ADD(`start_date`, INTERVAL(8-DAYOFWEEK(`start_date`)) DAY) last_date
FROM `events` GROUP BY weeks;

Answer 2

So it won't work?

SELECT
  DATE_FORMAT(`start_date`, '%x-%v') weeks,
  MAX(`start_date`), MIN(`start_date`)
  COUNT(*)
FROM `events` GROUP BY weeks;

Answer 3

Group with lines - IMHO will be a bit slow.
If in a locale, I would time it like this: form a week string, a numerical week number, a counter; would group by the numerical number of the week, would select two columns in the application.
If the load on the network is too large due to the numerical week number, I would leave it as you have, except that the line format would be changed to the one you need.