mysql>
mysql>
mysql> CREATE TABLE mytable (
-> id int(11) NOT NULL default '0',
-> choice tinyint(4) NOT NULL default '0',
-> ts timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO mytable VALUES (1,4,'2003-01-14 15:46:18'),
-> (2,1,'2003-01-14 15:49:44'),
-> (3,4,'2003-01-14 15:49:50'),
-> (4,4,'2003-01-14 15:49:53'),
-> (5,4,'2003-01-14 15:49:54'),
-> (6,2,'2003-01-14 15:49:58');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql>
mysql> SELECT DATE_FORMAT(ts, '%Y-%m') AS mnth,
-> SUM(IF(choice=1, 1, 0)) AS c,
-> SUM(IF(choice=2, 1, 0)) AS java,
-> SUM(IF(choice=3, 1, 0)) AS perl,
-> SUM(IF(choice=4, 1, 0)) AS php,
-> SUM(IF(choice=5, 1, 0)) AS vb,
-> SUM(IF(choice=6, 1, 0)) AS other,
-> COUNT(*)
-> FROM mytable
-> GROUP BY mnth;
+---------+------+------+------+------+------+-------+----------+
| mnth | c | java | perl | php | vb | other | COUNT(*) |
+---------+------+------+------+------+------+-------+----------+
| 2003-01 | 1 | 1 | 0 | 4 | 0 | 0 | 6 |
+---------+------+------+------+------+------+-------+----------+
1 row in set (0.00 sec)
mysql>
mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)