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

eagles396 · #1 (**permalink**) 02-04-12, 18:32

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

r937 · #2 (**permalink**) 02-04-12, 20:58

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

eagles396 · #3 (**permalink**) 02-04-12, 21:12

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

r937 · #4 (**permalink**) 02-04-12, 21:57

Code:

SELECT link
     , COUNT(*)
  FROM daTable
 WHERE clickdate >= '2012-01-01'
   AND clickdate  < '2012-02-01'
GROUP
    BY link

eagles396 · #5 (**permalink**) 02-04-12, 22:17

sorry I meant all I need is the first query grouped by the link

r937 · #6 (**permalink**) 02-04-12, 23:05

okay, now you've lost me

did you test the last query i gave you? how is it different than what you really wanted?

it-iss.com · #7 (**permalink**) 02-06-12, 03:29

By the way you can also use:

SELECT COUNT(DISTINCT ip, clickdate)
FROM daTable
WHERE clickdate >= '2012-01-01'
AND clickdate < '2012-02-01'

r937 · #8 (**permalink**) 02-06-12, 06:07

Quote:

Originally Posted by it-iss.com

By the way you can also use:

only in mysql

it-iss.com · #9 (**permalink**) 02-06-12, 06:14

and Oracle.

eagles396 · #10 (**permalink**) 02-06-12, 07:41

Quote:

Originally Posted by r937

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

r937 · #11 (**permalink**) 02-06-12, 08:28

Quote:

Originally Posted by eagles396

...but to also include the link column.

what happened when you included the link column?

Internet Services

Web Development

Digital Marketing

Consumer Tech