Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 37.9Mb
PDF (RPM) - 32.8Mb
HTML Download (TGZ) - 8.0Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 6.9Mb
Man Pages (TGZ) - 132.7Kb
Man Pages (Zip) - 189.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 8.0 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.

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.