SQL: Count the number of unique occurrences?

Question

I have a list of IDs and IP addresses and I want to display the amount of times an ID is associated with more than 1 IP address.

For example:

Data Set:

 ID     IP Address
 123    1.1.1.1
 123    2.2.2.2
 456    3.3.3.3
 456    4.4.4.4
 456    5.5.5.5

Expected Output:

 ID    Count of Distinct IP addresses
 123   2
 456   3

How can I accomplish this using SQL?

Thanks in advance!

You can use `COUNT(DISTINCT IP)`. – Giorgos Betsos Jun 15 '17 at 14:26 — Giorgos Betsos, Jun 15 '17 at 14:26

Rion Williams · Accepted Answer · 2017-06-15T14:35:10.887

5

Since you are going to be "grouping" these values based on a given column, you can take advantage of SQL's GROUP BY function that will allow you to create groups on a specific criteria and then perform aggregate operations on those individual groups:

  SELECT ID,
         COUNT(DISTINCT IpAddress)
    FROM YourTable
GROUP BY ID
  HAVING COUNT(DISTINCT IpAddress) > 1

The following sections are important:

GROUP BY - Since you are concerned with "groups" of IP Addresses based on ID, you'll need to separate your individual sets into these groups.
COUNT(DISTINCT IpAddress) - This is important within the context of an individual group, as you'll want to know how many "unique" IP Addresses are present in the group (you can remove the DISTINCT if you don't care about unique results).
HAVING - Since you are dealing with groups / aggregates, you'll need to use HAVING to indicate which groups to include (i.e. only include groups that have more than one distinct IP Address)

edited Jun 15 '17 at 14:35

answered Jun 15 '17 at 14:27

Rion Williams

74,820
37
200
327

Thank you for the input. I get an error that says: Syntax error (missing operator) in query expression 'COUNT(DISTINCT [IP Address])'. Am I doing anything wrong? Below is my code: SELECT [Unique ID], COUNT(DISTINCT [IP Address]) FROM Report3 GROUP BY [Unique ID] HAVING COUNT(DISTINCT [IP Address]) > 0; I appreciate your help – user2946613 Jun 15 '17 at 14:58
The `DISTINCT column_name` syntax should work as expected within the `COUNT()` function as shown above. What specific flavor of SQL are you using (i.e. SQL Server, MySQL, etc.)? – Rion Williams Jun 15 '17 at 15:12
I am using access and clicking on the SQL button to type out a query. Does that make a difference? – user2946613 Jun 15 '17 at 15:14
The syntax for Access may differ than that of other flavors of SQL as it doesn't appear to support the `COUNT(DISTINCT column_name)` syntax and instead has to use an approach [like this one](http://web.archive.org/web/20120805022223/http://blogs.office.com/b/microsoft-access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx). You _could_ try using `COUNT(*)` within your SELECT statement to see if that works (if you think your existing IDs shouldn't have any duplicates to begin with). – Rion Williams Jun 15 '17 at 15:19
hmm ok the problem is that the existing IDs will be repeated on any line there is a distinct IP address so it sounds like this might not work. I'll have to find another approach to access or download SQL server. Thanks for your advice! – user2946613 Jun 15 '17 at 15:28
Well the `COUNT(*)` operation would be scoped to each individual group, so it would really only be an issue if you had essentially multiple duplicate rows (i.e. same ID and same IP) as they would count multiple times, otherwise, it should give you the correct results. Alternatively, you could probably use a workaround similar to the ones mentioned in that earlier article (or [here](https://stackoverflow.com/a/11880300/557445)), which aren't very pretty but should work. – Rion Williams Jun 15 '17 at 15:46

Arion · Answer 2 · 2017-06-15T14:31:44.553

2

You can count distinct ip address by id. Like this:

SELECT ID,COUNT(DISTINCT IPAddress) AS nbr FROM table GROUP BY ID

Update

SELECT ID,COUNT(DISTINCT IPAddress) AS nbr 
FROM table 
GROUP BY ID
HAVING COUNT(DISTINCT IPAddress) > 1

edited Jun 15 '17 at 14:31

answered Jun 15 '17 at 14:27

Arion

31,011
10
70
88

If you read OP's question carefully you'll see that you miss a WHERE there or a HAVING. – Razvan Dumitru Jun 15 '17 at 14:29
@RazvanDumitru : you are right. I will update with a having – Arion Jun 15 '17 at 14:33

score 0 · Answer 3 · edited Jun 15 '17 at 21:55

0

You can use GROUP BY like this:

SELECT ID,COUNT(*)
FROM table_name
GROUP BY ID;

edited Jun 15 '17 at 21:55

Asif Raza

3,435
2
27
43

answered Jun 15 '17 at 14:34

aoter

11
4

SQL: Count the number of unique occurrences?

3 Answers3