SQL return last 12 weeks data, grouped by week, starting last Monday

Question

I've been working on a MySQL query that sorts data into weeks but I just can't figure out how to do it.

I would like to sort the data into weeks for the current and last 11 weeks. Each week will run from Monday 00:00:00 to Sunday 23:59:59.

(Taking todays date as 2014-12-04)...

Week 1: 2014-12-01 > 2014-12-07 - (Last Monday 00:00:00 to next Sunday 23:59:59)

Week 2: 2014-11-24 > 2014-11-30 - (Monday before last 00:00:00 to last Sunday 23:59:59)

Week 3: 2014-11-17 > 2014-11-23 - (Monday before before last 00:00:00 to last last Sunday 23:59:59)

And so on...

For each week the value field data will be totalled.

I need the data returned to be in the format:

datetime: The first date (Always a Monday) of that week.

value: The total of all the values in that week.

For example, the returned data:

Week 1: 2014-12-01 : Totalled value=11

Week 2: 2014-11-24 : Totalled value=3

Week 3: 2014-11-17 : Totalled value=9

Week 4: 2014-11-10 : Totalled value=7

Table_1 data:

table1id  datetime               value
1         2014-09-01 06:00:00    4
2         2014-09-04 17:00:00    6

3         2014-09-09 18:00:00    9

4         2014-09-15 07:00:00    4
5         2014-09-20 10:00:00    2

6         2014-09-25 10:00:00    3

7         2014-09-30 09:00:00    8
8         2014-10-01 14:00:00    5
9         2014-10-05 10:00:00    7

10        2014-10-09 18:00:00    3

11        2014-10-15 05:00:00    4

12        2014-10-20 07:00:00    8
13        2014-10-24 16:00:00    9

14        2014-10-29 15:00:00    5
15        2014-10-31 16:00:00    7

16        2014-11-05 09:00:00    2

17        2014-11-10 08:00:00    4
18        2014-11-15 16:00:00    3

19        2014-11-20 10:00:00    9

20        2014-11-25 10:00:00    2
21        2014-11-30 10:00:00    1

22        2014-12-01 15:00:00    7
23        2014-12-04 18:00:00    2

I 'could' just pull all the data unsorted for the date range using PHP and sort it from there but I'd rather the MySQL server do it.

Any suggestions would be greatly appreciated. :-)

Please update your post with what you have attempted so far. — Samuel Cook, Dec 04 '14 at 13:08

score 0 · Answer 1 · edited May 23 '17 at 10:33

based on generate days from date range you can do smething like that:

select mondays.week, mondays.day, sum(value)
from
    (select a.a+1 week, curdate() - WEEKDAY(curdate()) - INTERVAL (7*a.a) DAY as day from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11) as a) as mondays,
    Table_1
where Table_1.datetime between mondays.day and (mondays.day + interval(7) day)
group by mondays.week, mondays.day;

SQL return last 12 weeks data, grouped by week, starting last Monday

1 Answers1