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

Question

I'm trying to select data grouped by week, which I have working, but I need to be able to specify a different 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!

Answer 1

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.