MySQLで月別、日別、時間、曜日別にレコード数を集計する

月毎

SELECT
    DATE_FORMAT(regist_time, '%Y-%m') as regist_time,
    COUNT(*) as count
FROM
    users
GROUP BY
    DATE_FORMAT(regist_time, '%Y%m');

日毎

SELECT
    DATE_FORMAT(regist_time, '%Y-%m-%d') as regist_time,
    COUNT(*) as count
FROM
    users
GROUP BY
    DATE_FORMAT(regist_time, '%Y%m%d');

時間毎

SELECT
    DATE_FORMAT(regist_time, '%H') as regist_time,
    COUNT(*) as count
FROM
    users
GROUP BY
    DATE_FORMAT(regist_time, '%H');

曜日毎

SELECT
    DATE_FORMAT(regist_time, '%w') as regist_time,
    COUNT(*) as count
FROM
    users
GROUP BY
    DATE_FORMAT(regist_time, '%w');

※regist_timeは、曜日（0=Sunday..6=Saturday）になります。