1.日毎
group by date(date)
2.周毎
・日曜日開始
group by date(date+ interval 1 - DAYOFWEEK(date) day)
・月曜日開始
group by date(date- interval (5 + DAYOFWEEK(date))%7 day)
3.月毎
group by date(date+ interval 1 - DAYOFMONTH(date) day)
或いは
group by date_format(date, '%Y-%m')
※dateのデータ型はDATETIME