MySQLで日別集計

日別でデータが何件あるか?とか出す場面が最近よくある。
そんなときの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 |
+------------+----------+