Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Calculating Visits Per Day

3.6.8 Calculating Visits Per Day

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

             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),

The example table contains year-month-day values representing visits by users to the page. To determine how many different days in each month these visits occur, use this query:

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 query calculates how many different days appear in the table for each year/month combination, with automatic removal of duplicate entries.

Download this Manual
User Comments
  Posted by on May 17, 2005
It seems to me that it would be much simpler and intuitive to use something like this to query the days per month:

SELECT year, month, COUNT(DISTINCT day) AS days FROM t1 GROUP BY year,month;

  Posted by Anders Henke on December 19, 2005
Note that this example is not suitable for large-scale processing of weblogs ... it's missing indexes and usually one might use an aggregated counter if aggregated values are goal of the statistics.
Sign Up Login You must be logged in to post a comment.