MySQLで日別集計

            <div class="section">

日別でデータが何件あるか？とか出す場面が最近よくある。

そんなときのSQL。

CREATE TABLE `histories` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`user_id` int(10) NOT NULL,
`created` datetime NOT NULL,
`updated` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into histories values (null, 1, '2011-05-01 00:00:00', '2011-05-01 00:00:00');
insert into histories values (null, 1, '2011-05-01 00:00:00', '2011-05-01 00:00:00');
insert into histories values (null, 1, '2011-05-02 00:00:00', '2011-05-02 00:00:00');
insert into histories values (null, 1, '2011-05-02 00:00:00', '2011-05-02 00:00:00');
insert into histories values (null, 1, '2011-05-03 00:00:00', '2011-05-03 00:00:00');
select date(created) as date, count(*) from histories group by date;
-- select substring(created, 1,10) as date, count(*) from histories group by date;
+------------+----------+
| date       | count(*) |
+------------+----------+
| 2011-05-01 |        2 |
| 2011-05-02 |        2 |
| 2011-05-03 |        1 |
+------------+----------+