CODEWITHSUNDEEP
sqlgroup-by
Airikr
Airikr

Reputation: 6436

Grouping the ip addresses returns 15 instead of 3

I want to group the ip addresses so it returns 3 instead of 15 as it does with the SQL code below.

Here's how it looks like in my database:

ID    IDP   IDU  DATE                   INFO_IP
 1    19    0    2012-11-14 00:42:03    127.0.0.1
 2    19    0    2012-11-14 00:42:40    127.0.0.1
 3    19    0    2012-11-14 00:45:01    127.0.0.1
 4    19    0    2012-11-14 00:47:24    127.0.0.1
 5    19    0    2012-11-14 00:48:35    127.0.0.1
 6    19    0    2012-11-14 00:48:57    127.0.0.1
 7    19    0    2012-11-14 00:53:41    127.0.0.1
 8    19    0    2012-11-14 01:02:12    127.0.0.1
 9    19    0    2012-11-14 01:02:55    127.0.0.1
10    19    0    2012-11-14 01:03:03    127.0.0.1
11    19    0    2012-11-14 01:03:20    127.0.0.2
12    19    0    2012-11-14 01:03:28    127.0.0.2
13    19    0    2012-11-14 01:04:16    127.0.0.2
14    19    0    2012-11-14 01:04:33    127.0.0.3
15    19    0    2012-11-14 01:04:40    127.0.0.3

And here is the SQL code:

SELECT idp, COUNT(info_ip) FROM photos_watched
WHERE idp = '19'
GROUP BY info_ip
LIMIT 1

What have I missed?

Thanks in advance.

Upvotes: 0

Views: 47

Answers (2)

Vincent Savard
Vincent Savard

Reputation: 35927

What you want is to count the distinct IP :

SELECT COUNT(DISTINCT INFO_IP)
FROM photos_watched
WHERE idp = 19;

Your current query counts the number of IP in each group.

Upvotes: 1

ShyJ
ShyJ

Reputation: 4640

Shouldn't it be

SELECT info_ip, COUNT(*) 
FROM photos_watched
WHERE idp = '19'
GROUP BY info_ip

Upvotes: 0

Related Questions