How can I group by date in MySQL?

Question

The following MySQL query get all weeks data by user for different dates:

SELECT user_id,
       CONCAT(follow_up_date, ' - ', follow_up_date + INTERVAL 6 day) AS week,
       GROUP_CONCAT(follow_up_date)
FROM   feedback_2
WHERE  follow_up_date > ''
GROUP  BY WEEK(follow_up_date),
          user_id

Output:

---------------------------------------------------------------
user_id |         week           | group_concat(follow_up_date)|
---------------------------------------------------------------
4        2012-08-28 - 2012-09-03 | 2012-08-28,2012-08-28,2012-08-29
14       2012-08-28 - 2012-09-03 | 2012-08-28 

But my requirement is, how to separate different date for same user.

---------------------------------------------------------------
user_id |         week           | group_concat(follow_up_date)|
---------------------------------------------------------------
4        2012-08-28 - 2012-09-03 | 2012-08-28,2012-08-28
4        2012-08-28 - 2012-09-03 | 2012-08-29
14       2012-08-28 - 2012-09-03 | 2012-08-28 

Answer 1

Not tested , but you may try it:

SELECT user_id,
       _CONCAT(DATE_SUB(follow_up_date, INTERVAL _DAYOFWEEK(follow_up_date)-3
                                        day),
       ' - ', DATE_SUB(follow_up_date, INTERVAL _DAYOFWEEK(follow_up_date)-3 day
              )
       + INTERVAL 6 day) AS week,
       GROUP_CONCAT(follow_up_date)
FROM   feedback_2
WHERE  follow_up_date > ''
GROUP  BY WEEK(follow_up_date)

Please remove the _ character when parts of the words in the expression start so that the query will be correct.

Answer 2

SELECT  `user_id`, 
        CONCAT(`follow_up_date`, ' - ', `follow_up_date` + INTERVAL 6 DAY) AS f_week, 
        GROUP_CONCAT(`follow_up_date`) AS c_date
FROM feedback_2 
WHERE `follow_up_date` > ''
GROUP BY f_week, `user_id`
ORDER BY  `user_id`, f_week