Group rows by 7 days interval starting from a certain date

Question

Is there a way to group rows by a 7 days intervals(datetime) starting from a certain date in Mysql?

possible duplicate of [MYSQL - Group by Week Question](http://stackoverflow.com/questions/7492187/mysql-group-by-week-question) — CoolBeans, Jan 17 '13 at 15:48

ypercubeᵀᴹ · Accepted Answer · 2013-01-17T13:41:55.067

17

SELECT 
    1 + DATEDIFF(columnDate, @start_date) DIV 7  AS weekNumber
  , @start_date + INTERVAL (DATEDIFF(columnDate, @start_date) DIV 7) WEEK
      AS week_start_date
  , MIN(columnDate) AS actual_first_date
  , MAX(columnDate) AS actual_last_date
  , SUM(otherColumn)
  , AVG(otherColumn)
  --- 
FROM 
    tableX 
WHERE 
    columnDate >= @start_date 
GROUP BY
    DATEDIFF(columnDate, @start_date) DIV 7 ;

edited Jan 17 '13 at 13:41

answered Jan 17 '13 at 13:34

ypercubeᵀᴹ

113,259
19
174
235

It worked perfectly! I'm not quite confortable with date processing in Mysql but it seems that it's not so hard. Thank you! – flaviu Jan 17 '13 at 14:05
Does this require a row for every day? – Andrew Tibbetts Mar 24 '15 at 15:53
@AndrewTibbetts what do you mean? – ypercubeᵀᴹ Mar 24 '15 at 18:10
Does there need to be a row with a date for every day within the week for the datediff to work? In other words, does the `DIV 7` in the `GROUP BY` require 7 rows? – Andrew Tibbetts Mar 24 '15 at 21:31
@ypercubeᵀᴹ awesome answerdude. – Choxx Jun 13 '16 at 10:11
@ypercubeᵀᴹ: Do you have a way this can be done in sQL Server. I was looking to implement the same on SQL Server. Need to group on 7 days intervals. – Kashyap MNVL Sep 19 '16 at 20:24
@KashyapMNVL you could use `GROUP BY DATEDIFF(day, @start_date, columnDate) / 7`. – ypercubeᵀᴹ Sep 19 '16 at 21:04
Or just `GROUP BY DATEDIFF(week, @start_date, columnDate)` – ypercubeᵀᴹ Sep 19 '16 at 21:11
@ypercubeᵀᴹ: When I do that, I get the following error message on SSMS. "Each GROUP BY expression must contain at least one column that is not an outer reference." Any thoughts on this one? – Kashyap MNVL Sep 20 '16 at 17:29

mychalvlcek · Answer 2 · 2013-01-17T13:20:11.793

3

SELECT *
FROM  `table` 
GROUP BY WEEK( ADDDATE( `date_column` , WEEKDAY(NOW()) ) )

edited Jan 17 '13 at 13:20

answered Jan 17 '13 at 12:46

mychalvlcek

3,956
1
19
34

1

it's pretty close, but this gives me the weeks starting with Sunday or Monday. I need the starting day to be the weekday of the given date. – flaviu Jan 17 '13 at 12:49

score 0 · Answer 3 · answered Jan 17 '13 at 12:41

0

SELECT users.* from users 
WHERE created_at >= '2011-12-01' 
AND created_at <= date_add('2011-12-01', INTERVAL 7 DAY)

This selects the users created between 201-12-01 and 7 days after that. Make changes, to query based on your need.

answered Jan 17 '13 at 12:41

Prasanth Bendra

31,145
9
53
73

thank you for the quick answer! But this gives me only the first 7 day interval. I need all 7 day intervals starting from that date. – flaviu Jan 17 '13 at 12:44

Group rows by 7 days interval starting from a certain date

3 Answers3

Linked