MYSQL Help - Count Distinct IP Addresses per day 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

 

I think what you need is this:
COUNT (DISTINCT ip_address)

so in MS SQL syntax I would do it like this (see the code below). I am sure similar concept exists in mySQL

select 
date_with_truncated_time, count(distinct ip_address)
where by date_with_truncated_time between @StartDate and @EndDate
and link_url = @my_target_url
group by date_with_truncated_time
order by date_with_truncated_time

Code (markup):

 

SELECT date,count(distinct ip)
FROM test.test
GROUP BY date

Was it what you were looking for?

 

SELECT MONTHNAME(date),count(distinct ip)
FROM test.test
GROUP BY MONTHNAME(date)

?

 

alexmoblie has the correct solution. the query is same for mysql too. you seem to accidentally added "by" after where in the sql. apart from that the query is ok and same in mysql. But the long names that you gave to the column names might confuse the beginner. It is better to use simple and short names.

Both the queries by sketchx are not for what cashbackmedia asked for. I guess, you understood the problem in different way.

 

oups..it was per day, I thought it was per month

 

yes great thought