Vincent Vincent - 3 months ago 7
MySQL Question

How to select distinct ip for each day

I have a table called logvisit with fields including ip (varchar(30)) and date (datetime) :

ip date
1.1 2016-08-23 00:05:40
1.1 2016-08-24 00:05:15
1.1 2016-08-24 00:05:20
1.2 2016-08-22 00:01:00
1.2 2016-08-22 00:00:30
1.2 2016-08-23 00:01:00


EDIT : I want to SELECT all distinct ip by DAY. One IP can have several days (if the user comes three days in a row), and one day can have several IP (because I have more than one user). I just don't want the same IP on the same day.

Expected output (1 ip / 1 day) :

ip day
1.1 2016-08-23
1.1 2016-08-24
1.2 2016-08-22
1.2 2016-08-23


I found this thread SQL group by day, with count so as a beginning I tried :

SELECT
Convert(char(8), date, 112),
count(distinct ip)
FROM logvisit
GROUP BY Convert(char(8), date, 112)


which gave me an unexpected "Syntax error near 112), count(distinct ip"..

Answer

You need to group by both ip & date

SELECT 
  DATE(date) AS d,
  count(ip)
FROM logvisit
GROUP BY d,ip

Note: Date(time_stamp/datetime) will extract the date part.


If you want to count the distinct ip per day then the following query would be a use :

SELECT 
  DATE(date) AS d,
  count(distinct ip) AS dayWiseDistinctIpCount
FROM logvisit
GROUP BY d

EDIT:

In order to get distinct pair of <ip,day> :

SELECT 
 DISTINCT ip,
 DATE(date) AS day
FROM logvisit