Return to Answer

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.

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.

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

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

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

If you have a different day of the week as the end of week, let me know and I will rewrite the query.

Give it a Try !!!

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.

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

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

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

If you have a different day of the week as the end of week, let me know and I will rewrite the query.

Give it a Try !!!

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

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

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

If you have a different day of the week as the end of week, let me know and I will rewrite the query.

Give it a Try !!!