Use BIT_COUNT function : BIT Count « Function « SQL / MySQL

1. SQL / MySQL
2. Function
3. BIT Count

Use BIT_COUNT function

/*
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;

           
       

Related examples in the same category