GNU Info

(mysql.info)Calculating days

---

Prev: Searching on two keys Up: Examples

---

Enter node , (file) or (file)node

Calculating visits per day
--------------------------

The following shows an idea of how you can use the bit group functions
to calculate the number of days per month a user has visited a web page.

     CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL);
     INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),(2000,2,23),(2000,2,23);
     
     SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;
     
     Which returns:
     
     +------+-------+------+
     | year | month | days |
     +------+-------+------+
     | 2000 |    01 |    3 |
     | 2000 |    02 |    2 |
     +------+-------+------+

The above calculates how many different days was used for a given
year/month combination, with automatic removal of duplicate entries.