SQL Query to get last day of every week
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.