CODEWITHSUNDEEP
sqlsql-server
user2903089
user2903089

Reputation: 313

SQL Count query on how many fields have another field with a value

I have a log table that has includes columns named IP and ID in SQLSERVER

Now some IP's share the same ID and what I need to do is get a result set back that gives me a count of how many distincts IP's where found for each ID. This one has me a little fooled at the moment can anyone please help

so if I have

IP           ID
129.168.0.2, 12
192.168.0.3, 12
1292.68.0.3, 1

I want a result set that shows 

COUNT, ID
2,     12
1,     1

Upvotes: 1

Views: 1108

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43474

You need to use a group by clause with a distinct for the IP:

SELECT COUNT(DISTINCT IP), ID FROM table1
GROUP BY ID

Otherwise, you would be counting all the IPs instead of the unique ones.

Upvotes: 1

Iswanto San
Iswanto San

Reputation: 18569

You can use aggregate function.

Try this:

select count(distinct IP), ID from table1
group by ID;

Upvotes: 1

Related Questions