Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2006
    Posts
    4

    Unanswered: Count Distinct (Per day) IP Addresses for a date range

    Hi,

    I am looking for some help to create a mysql query to get the distinct (per day) count of ip addresses a link has been clicked for a date range.

    Lets say my IP address clicks on the same link 5 times every day during January 2012 using the same ip address. There would be 155 (5*31) records in the table but the query result should display 31 as we are only counting the same ip address max of once per day.


    Any help would be appreciated

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT COUNT(*) AS distinct_count
      FROM ( SELECT DISTINCT
                    ip
                  , DATE(clickdate)
               FROM daTable
              WHERE clickdate >= '2012-01-01'
                AND clickdate  < '2012-02-01' ) AS t
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2006
    Posts
    4
    Thanks. How would you group by the Link so the query would look like below

    Link Column, Count Column
    Link 1, 10
    Link 2, 20
    etc..

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT link
         , COUNT(*)
      FROM daTable
     WHERE clickdate >= '2012-01-01'
       AND clickdate  < '2012-02-01'
    GROUP
        BY link
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2006
    Posts
    4
    sorry I meant all I need is the first query grouped by the link
    Last edited by eagles396; 02-04-12 at 23:24.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, now you've lost me

    did you test the last query i gave you? how is it different than what you really wanted?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    By the way you can also use:

    SELECT COUNT(DISTINCT ip, clickdate)
    FROM daTable
    WHERE clickdate >= '2012-01-01'
    AND clickdate < '2012-02-01'
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by it-iss.com View Post
    By the way you can also use:
    only in mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    and Oracle.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  10. #10
    Join Date
    Dec 2006
    Posts
    4
    Quote Originally Posted by r937 View Post
    okay, now you've lost me

    did you test the last query i gave you? how is it different than what you really wanted?
    Sorry I was looking for something like your first query which I have listed below but to also include the link column.

    Code:
    SELECT COUNT(*) AS distinct_count
      FROM ( SELECT DISTINCT
                    ip
                  , DATE(clickdate)
               FROM daTable
              WHERE clickdate >= '2012-01-01'
                AND clickdate  < '2012-02-01' ) AS t

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by eagles396 View Post
    ...but to also include the link column.
    what happened when you included the link column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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