SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Nov 2001
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting a total count of unique records within a time period

    I store web site traffic information in MySQL and when I want to get the running total number of unique IP addresses in a year to a specific page I can do this:

    Code:
     SELECT COUNT(DISTINCT(ip_address)) as total 
     FROM pagestats 
     WHERE pageid = 100 
     AND year = 2006
    What I really want to do is get the total of unique IP addresses by day, then totalled for the year. In this way one IP can come back every day for the whole year and still count as 365 unique IPs. I know the slow way to do this is to actually do a query for each day, then total up the results, but I'm hoping there is a quicker method.

    Edit: I have a 'day' column in the table for an integer day of the month (eg. 31) as well as a timestamp column (eg. 2006-01-31 15:34:10)

    Thanks.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select sum(d_ip) as theCount
      from (select count(distinct ip_address) d_ip
              from pagestats
            group
                by day
                 , year) dt

  3. #3
    SitePoint Addict
    Join Date
    Nov 2001
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    Code:
    select sum(d_ip) as theCount
       from (select count(distinct ip_address) d_ip
               from pagestats
             group
                 by day
                  , year) dt
    Thanks, that got me there. To be honest I forgot that MySQL supported subselects since I've been using 4.0 for so long. Luckily I am on 4.1 now


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •