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

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