retrieve unique visits sql not working mysql

I have this table

 visitors: ip,last_date (datetime)

When a different ip enters to the site in an specific day, it insert a new row, no matter if the ip is entering again.

Whit this sql statement I retrieve all unique ip visits in my site. It works for an specific day, for example today (26/05/2020) and it grouped it by ip

  SELECT  v.*, max(last_date) as last_date1
  FROM visitors as v
  WHERE date(v.last_date) = :today
  GROUP BY v.ip 
  ORDER BY last_date1 DESC

Now I want to make an small daily report

 SELECT 
 COUNT(*) as count,
 DATE(last_date) AS last_date1
 FROM visitors 
 WHERE 1
 GROUP BY YEAR(last_date), MONTH(last_date), DAY(last_date)
 ORDER BY last_date1 DESC

count retrieves all the times a especific ip has entered during the whole day. I jut want it to count it as 1.

OUTPUT

26/05/2020  76455
25/05/2020  34561
24/05/2020  45642

I want to retrieve unique ip visits. not the sum of the times every ip has entered during the day.

EXPECTED RESULT

26/05/2020  676
25/05/2020  543
24/05/2020  711

How can I fix this?

Answer

I think that you want aggregation and count(distinct ip).

select date(last_date) visit_day, count(distinct ip) no_visiting_ip
from visitors   
group by date(last_date)
order by visit_day desc

This counts how many unique ips visited the site on each day. If you want the overall count of visits (regardless of whether the same ip visited more than once), then you can use count(*) instead.

Advertisement

Answer