MYSQL Group By with 日付

select count(*), DATE_FORMAT(date_field_name,"%Y-%m-%d") as created_day FROM table_name GROUP BY created_day

select count(*), DATE_FORMAT(date_field_name,"%Y-%m") as created_month FROM table_name GROUP BY created_month
select count(*), DATE_FORMAT(date_field_name,"%Y") as created_year FROM table_name GROUP BY created_year
※ %Y-%m-%d 等の部分はそのまま使い、数字などに変更しない。
 

他の方法

月別の取得

SELECT SUM(field) FROM table GROUP BY DATE_FORMAT(date_field, ‘%Y%m’);

日付別の取得

SELECT SUM(field) FROM table GROUP BY DATE_FORMAT(date_field, ‘%Y%m%d’);

曜日別の取得

SELECT SUM(field) FROM table GROUP BY WEEKOFDAY(date_field);