SQL Query to get last day of every week

Using MySQL, I need to query a table that has a datetime field. I need to count all the records for a particular week. Using date_format(colname, "%Y %V") returns the week #, but I need the last day (day of month 01-28|29|30|31) for that week.

Output E.g.

COUNT(*) | TheDate
11 | 2012-01-07
22 | 2012-01-14
123 | 2012-01-21

Help?

Note: Last day of the week in my case is Sunday.

You need to use the DAYOFWEEK function

If the End of the Week in Saturday, any date can be morphed into a Saturday.

For example, this computes the upcoming Saturday

SELECT DATE(NOW() + INTERVAL (7 - DAYOFWEEK(NOW())) DAY);

This computes the upcoming Sunday (you must use WEEKDAY function instead)

SELECT DATE(NOW() + INTERVAL (6 - WEEKDAY(NOW())) DAY);

For a table called mytable with a Date Column called theDate, your summation would be

Saturday

SELECT COUNT(1) DateCount,EndOfWeekDate FROM
(
    SELECT DATE(theDate + INTERVAL (7 - DAYOFWEEK(theDate)) DAY) EndOfWeekDate
    FROM mytable
) A GROUP BY EndOfWeekDate;

Sunday

SELECT COUNT(1) DateCount,EndOfWeekDate FROM
(
    SELECT DATE(theDate + INTERVAL (6 - WEEKDAY(theDate)) DAY) EndOfWeekDate
    FROM mytable
) A GROUP BY EndOfWeekDate;

Give it a Try !!!

CAVEAT if you want any other end of week other than Saturday and Sunday, I wrote an insane algorithm back in Sep 22, 2011 for computing any week starting and ending whatever day.

StackExchange Database Administrators Q#15742, answer score: 13