How to select distinct ip for each day

Question

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

1000111 · Accepted Answer · 2016-08-24T09:34:59.360

2

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

edited Aug 24 '16 at 09:34

answered Aug 24 '16 at 09:22

1000111

13,169
2
28
37

Sorry, I mislead you. Your answer is syntaxically correct but I have only one IP by day. What I need is ALL distinct ip, for each day, not just one ip for each distinct day (see my edit in post) – Vincent Aug 24 '16 at 09:33

score 1 · Answer 2 · answered Aug 24 '16 at 09:45

1

SELECT DISTINCT ip, DATE(DATE) as day FROM logvisit GROUP BY DATE(DATE),ip

answered Aug 24 '16 at 09:45

bhanu avinash

484
2
16

score 0 · Answer 3 · answered Aug 24 '16 at 09:22

0

Try something like this:

SELECT id, DATE(`date`) AS day, COUNT(DISTINCT ip) AS total_visits
FROM logvisit
GROUP BY DATE(`date`);

answered Aug 24 '16 at 09:22

Kostas Mitsarakis

4,772
3
23
37

Sorry, I mislead you. Your answer is syntaxically correct but I have only one IP by day. What I need is ALL distinct ip, for each day, not just one ip for each distinct day (see my edit in post) – Vincent Aug 24 '16 at 09:33

How to select distinct ip for each day

3 Answers3