Group SQL results by week and specify "week-ending" day

I'm trying to select data grouped by week, which I have working, but I need to be able to specify a di开发者_C百科fferent day as the last day of the week. I think something needs to go near INTERVAL (6-weekday('datetime')) but not sure. This kind of SQL is above my pay-grade ($0) :P

SELECT 
    sum(`value`) AS `sum`, 
    DATE(adddate(`datetime`, INTERVAL (6-weekday(`datetime`)) DAY)) AS `dt` 
FROM `values` 
WHERE id = '123' AND DATETIME BETWEEN '2010-04-22' AND '2010-10-22' 
GROUP BY `dt` 
ORDER BY `datetime`

Thanks!

select
    sum(value) as sum,
    CASE WHEN (weekday(datetime)<=3) THEN date(datetime + INTERVAL (3-weekday(datetime)) DAY)
        ELSE date(datetime + INTERVAL (3+7-weekday(datetime)) DAY)
    END as dt
FROM values
WHERE id = '123' and DATETIME between '2010-04-22' AND '2010-10-22'
GROUP BY dt
ORDER BY datetime

This does look pretty evil but, this query will provide you with a sum of value grouped by a week ending on a Thursday (weekday() return of 3).

If you wish to change what day the end of the week is you just need to replace the 3's in the case statement, ie if you wanted Tuesday you would have it say

CASE WHEN (weekday(datetime)<=1) THEN date(datetime + INTERVAL (1-weekday(datetime)) DAY)
        ELSE date(datetime + INTERVAL (1+7-weekday(datetime)) DAY)

I hope this helps.

Simple solution that I like. This will return the date for the start of the week assuming the week ends Sunday and starts Monday.

DATE(`datetime`) - INTERVAL WEEKDAY(`datetime`) AS `dt` 

This can easily be adjusted to have a week ending on Thursday because Thursday is 3 days earlier than Sunday

DATE(`datetime`) - INTERVAL WEEKDAY(`datetime` + INTERVAL 3 DAY) AS `dt` 

this returns for the start of the week that starts on Friday and ends on Thursday.

You can group on this no problem. If you want to use get the end of the week based on the start you do this

DATE(`datetime`) - INTERVAL -6 + WEEKDAY(`datetime` + INTERVAL 3 DAY) AS `dt` 

I think you must choose between Sunday and Monday? When you can use DATE_FORMAT for grouping by string format of date, and use %v for grouping by Mondays and %v for grouping by Sundays.

SELECT 
    sum(`value`) AS `sum`, 
     DATE_FORMAT(`datetime`,'%v.%m.%Y') AS `dt` 
FROM `values` 
WHERE id = '123' AND DATETIME BETWEEN '2010-04-22' AND '2010-10-22' 
GROUP BY  DATE_FORMAT(`datetime`,'%v.%m.%Y')
ORDER BY `datetime`

How to use DATE_FORMAT

I don't remember the exact math, but you can get WEEKDAY to wrap around on different days of the week by adding or subtracting days to its argument. You'll need to tinker with different values of x and y in the expression:

x-weekday(adddate(`datetime`, INTERVAL y DAY))