Group By date Mysql

MySQL Group By Seven Days

SEPTEMBER 13, 2010 9 COMMENTS

If we have a daily expense table like

1 2 3 4 5 6 7 8 9 10 11 12 13 14

+------+------------+ | cost | date       | +------+------------+ |   44 | 2010-07-30 | |   39 | 2010-07-31 | |   18 | 2010-08-01 | |   71 | 2010-08-02 | |   69 | 2010-08-03 | |   62 | 2010-08-04 | |   89 | 2010-08-05 | |   72 | 2010-08-06 | |   46 | 2010-08-07 | |   23 | 2010-08-08 | +------+------------+

its very easy to show monthly aggregation. Just a group by clause on date column based on month will do the job

1 2 3 4

SELECT  SUM(cost) AS total, MONTHNAME(date) AS month FROM daily_expense GROUP BY month ORDER BY MONTH(date);

1 2 3 4 5 6 7

+-------+-----------+ | total | month     | +-------+-----------+ |    83 | July      | |  1641 | August    | |   554 | September | +-------+-----------+

Now what we do when its been asked to show weekly aggregation. Using MySQL WEEK function returns the number of the week number of the date. By default the start of the week is Sunday or Monday based on the Server System Variables. Otherwise it can be set while using the function itself. So the query

1 2 3 4

SELECT  SUM(cost) AS total, CONCAT(date, ' - ', date + INTERVAL 6 DAY) AS week FROM daily_expense GROUP BY WEEK(date) ORDER BY WEEK(date)

gives something like

1 2 3 4 5 6 7 8 9 10 11 12

+-------+-------------------------+ | total | week                    | +-------+-------------------------+ |    83 | 2010-07-31 - 2010-08-06 | |   427 | 2010-08-07 - 2010-08-13 | |   345 | 2010-08-14 - 2010-08-20 | |   389 | 2010-08-21 - 2010-08-27 | |   318 | 2010-08-28 - 2010-09-03 | |   404 | 2010-09-04 - 2010-09-10 | |   272 | 2010-09-11 - 2010-09-17 | |    40 | 2010-09-13 - 2010-09-19 | +-------+-------------------------+

Till now its fairly straightforward and simple. Unfortunately it does not remain so when the same weekly aggregation has to be done but on different start day of the week, other than Sunday or Monday. Is there any option in MySQL to specify different start day of a week? I couldn’t find it, but if anybody knows anything like this please let me know by putting a comment here.
But I couldn’t wait till someone finds a easier way & let me know how. I have my own solution.

The thought
To get any aggregated data from a table we have to use the Group By clause on that field. In our case the field is the day name of a day of a week, like Wednesday, lets say in our case. My plan is to do some kind of operation on all the day up to next Wednesday. This function will change those next days into something similar to Wednesday. Similarly, another function will change last weekdays (like Tuesday, Monday) up to last Wednesday into something similar and unique to the last Wednesday.

Now, what is uniqueness of Wednesday which can be treated logically !! The first thing came to my mind is the day number of the week. MySQL WEEKDAY function gives a number for each day corresponding to the position of the day in a week. Like 2 is for Wednesday. If we use some function to make all other days as 2, we will ed up having all the day in a year as 2.
So, what is the uniqueness of each Wednesday from the other!, across the time! Here MySQL have another function named TO_DAYS. This function gives a unique number for each day since year 0. Now, my plan is to create some operations which for each day gives the same number equals to its previous Wednesday. So from one Wednesday to the next the number remains same and I can apply Group By clause over them to get an weekly aggregation.

The execution
I have an array of weekdays where Monday is 0 & Sunday is 6, same as the WEEKDAY function in MySQL.

1 2 3 4 5 6 7 8

$weekArr = array(             'Monday' => 0,             'Tuesday' => 1,             'Wednesday' => 2,             'Thursday' => 3,             'Friday' => 4,             'Saturday' => 5,             'Sunday' => 6);

So given the weekday, which should be considered as the start day of a week, I can get a number (2 for Wednesday). All the date following it till Sunday has the weekday greater than current Wednesday. So I subtract the day difference between the day and the current Wednesday from the TO_DAYS of the day to make it same as the TO_DAYS of the current Wednesday. I do something like

1 2	$startWeekDay = $weekArr['Wednesday']; //2 TO_DAYS(date) - (WEEKDAY(date) - ".$startWeekDay.")

And for all the preceding days up to previous Wednesday, I subtract the day difference between the day and its previous Wednesday. Now the previous Wednesday has exact 7 days different from the current Wednesday. Like

1	TO_DAYS(date) - (7 - (".$startWeekDay." - WEEKDAY(date)))

And the whole query becomes

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT  SUM(cost) AS total,         CONCAT(IF(date - INTERVAL 6 day < '".$startDay."',                 '".$startDay."',                 IF(WEEKDAY(date - INTERVAL 6 DAY) = ".$startWeekDay.",                     date - INTERVAL 6 DAY,                     date - INTERVAL ((WEEKDAY(date) - ".$startWeekDay.")) DAY)),             ' - ', date) AS week,             IF((WEEKDAY(date) - ".$startWeekDay.") >= 0,                 TO_DAYS(date) - (WEEKDAY(date) - ".$startWeekDay."),                 TO_DAYS(date) - (7 - (".$startWeekDay." - WEEKDAY(date)))) AS sortDay FROM    daily_expense WHERE   date BETWEEN '".$startDay."' AND '".$endDay."' GROUP BY sortDay;

which gives

1 2 3 4 5 6 7 8 9

+-------+-------------------------+---------+ | total | week                    | sortDay | +-------+-------------------------+---------+ |   158 | 2010-08-01 - 2010-08-03 |  734346 | |   378 | 2010-08-04 - 2010-08-10 |  734353 | |   365 | 2010-08-11 - 2010-08-17 |  734360 | |   393 | 2010-08-18 - 2010-08-24 |  734367 | |   347 | 2010-08-25 - 2010-08-31 |  734374 | +-------+-------------------------+---------+

Now a date range can be put to limit the results like, BETWEEN ‘2010-08-01’ AND ‘2010-08-31’.

I believe this can be extended or modified to use any interval of aggregation (thats may be the next part), just have to find a logical equation for each step of the interval.