Skip to content
Advertisement

Mysql queries for unique and returning visitors

I am making a chart that will show views, unique visitors and returning visitors of the website for each day. On my main page I am logging every visitors IP and date when it was recorded. For the chart to work I need 3 MySQL queries, each one showing one value for each day.

The first query would be something like this:
SELECT DATE_FORMAT(date,'%y-%m-%d'),COUNT(ip) FROM visits GROUP BY DATE_FORMAT(date,'%y-%m-%d')
This query will simply output nuber of ip adresses logged per day.

The two following queries will be harder. For the second one I need to count each IP adress only once per day
For the third one I need to count only IP adresses that are already in the database.

I found that the third one might be done using something like this

SELECT date, 
   visits.ip 
FROM visits
   INNER JOIN (SELECT ip
               FROM   visits
               GROUP  BY ip
               HAVING COUNT(id) > 1) dup
           ON visits.ip = dup.ip

However I couldn’t figure out how to implement counting the records into this one.
How can I make the second and the third query?
I am also open to PHP solutions

Advertisement

Answer

I would rewrite your first query to use the date() function to remove the time portion of the date:

select date(v.date) date_day, count(v.ip) visits
from visits v
group by date_day

Your second query is just count(distinct ...):

select date(v.date) date_day, count(distinct v.ip) distinct_visits
from visits v
group by date_day

The third query could make use of exists:

select date(v.date) date_day, count(distinct v.ip) returning_visits
from visits v
where exists (select 1 from visits v1 where v1.ip = v.ip and v1.date < date(v.date))
group by date_day
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement