MySQL 週毎にまとめる方法

MySQL

SELECT count(id),
       `date`,
       SUBDATE(`date`, WEEKDAY(`date`)) AS beginning
FROM test
GROUP BY beginning;

解説

WEEKDAY(date)

mysql> SELECT WEEKDAY('2008-02-03 22:23:00');
        -> 6
mysql> SELECT WEEKDAY('2007-11-06');
        -> 1

dateに対応する曜日インデックス(0 = Monday、1 = Tuesday、…6 = Sunday)を返します。

SUBDATE(expr,days)

-- 加算
mysql> SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
        -> '2007-12-02'

-- 減算
mysql> SELECT SUBDATE('2008-01-02 12:00:00', 31);
        -> '2007-12-02 12:00:00'

第1引数から第2引数分を、足したり引いたりします。
第2引数をINTERVAL形式で書くと加算し、数字だけ指定すると減算されます。

今回の場合は、WEEKDAY() の戻り値を渡しているので、dateから曜日インデックス分を引いています。

WEEKDAY('2020-01-13') -> 0 -> SUBDATE('2020-01-15', 0) -> '2020-01-13'
WEEKDAY('2020-01-16') -> 3 -> SUBDATE('2020-01-15', 3) -> '2020-01-13'
WEEKDAY('2020-01-19') -> 6 -> SUBDATE('2020-01-19', 6) -> '2020-01-13'

2020-01-13は月曜日のため、これでそれぞれの週の頭の日付けがわかります。
あとはGROUP BYで同じ日付をまとめればおしまいです。

参照：http://sqlfiddle.com/#!9/389f85/2