MySQL Group By Seven Days

If we have a daily expense table like

+------+------------+
| 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

SELECT	SUM(cost) AS total, MONTHNAME(date) AS month 
FROM daily_expense 
GROUP BY month 
ORDER BY MONTH(date);
+-------+-----------+
| 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

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

+-------+-------------------------+
| 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.

$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

$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

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

And the whole query becomes

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

+-------+-------------------------+---------+
| 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.

Advertisements

9 Responses to MySQL Group By Seven Days

  1. adi says:

    hi,
    i try this example: “CONCAT(date, ‘ – ‘, date + INTERVAL 6 DAY) AS week” but this generate error.

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘0’ at line 1.

    this code works fine in mysql but, as i try trough php page this code generate error above.
    where is the 0 comming from?

    • dyutiman says:

      your php code must be adding something with the query. print your query from php and check the output. or if u want me to take a look at your code please feel free to share.

  2. Avinash says:

    Very good example, this is what I was looking for. Thanks a lot. I did +1 for this page on G+.

  3. hitesh says:

    hi! great post.

    please try to answer this problem

    i want to display even those weeks on which entry is not there.

    for e.g see the following output
    12 2011-04-28 – 2011-05-04
    18 2011-06-11 – 2011-06-17
    17 2011-06-13 – 2011-06-19

    now there are weeks between first row and second row of the output, but they are not displayed as there are no entry on these dates.

    but i want to display all weeks starting from the start_date till end_date

    • dyutiman says:

      I did that part in view. To do the same in DB level, I think the whole thought process has to be changed.

  4. Peter Stannett says:

    HI

    I have tried your code but mysql seems to disregard the concat element and returns the sum of all the dates. Amending the date + INTERVAL 6 DAY does not affect the numbers returned. For example I get the same result returned if I have + INTERVAL 1 DAY.

    Could you help explain why this is the case?

    Thanks

  5. Girish says:

    Hi,
    This is an excellent example which I was searching. But I guess the example is combination of php & mysql. I want to do same within MySql only.

    • dyutiman says:

      if u know the start day of the week… u can hard code it in the query…. php is used here only for making the start day dynamic….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: