|
/*
mysql> /* how many different days in each month these visits occur
mysql> */
mysql> SELECT year, month, BIT_COUNT(BIT_OR(1<<day)) AS days FROM timeTable
-> GROUP BY year,month;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2001 | 01 | 1 |
| 2002 | 06 | 1 |
| 2003 | 05 | 1 |
| 2004 | 02 | 1 |
| 2005 | 04 | 1 |
| 2006 | 03 | 1 |
+------+-------+------+
6 rows in set (0.02 sec)
*/
Drop table timeTable;
CREATE TABLE timeTable (
year YEAR(4),
month INT(2) UNSIGNED ZEROFILL,
day INT(2) UNSIGNED ZEROFILL
);
INSERT INTO timeTable VALUES(2001,1,1),
(2002,6,20),
(2003,5,30),
(2004,2,2),
(2005,4,23),
(2006,3,23);
/* how many different days in each month these visits occur
*/
SELECT year, month, BIT_COUNT(BIT_OR(1<<day)) AS days FROM timeTable
GROUP BY year,month;
|