Count IP Addresses

  • Thread starter Thread starter pand0ra.usa
  • Start date Start date
P

pand0ra.usa

I have a query and I am not sure how to go about counting the number
of duplicate IP Addresses and sorting on the most found (i.e. I want
to see the IP Address that has the most duplicates at the top of the
list).




SELECT HeuristicsDaily.[IPAddress], HeuristicsDaily.[Logged],
HeuristicsDaily.[Account], HeuristicsDaily.[PIN]
FROM HeuristicsDaily
WHERE (((HeuristicsDaily.[IPAddress]) In (SELECT [IPAddress] FROM
[HeuristicsDaily] As Tmp GROUP BY [IPAddress] HAVING Count(*)>1 )))
ORDER BY HeuristicsDaily.[IPAddress];
 
Group on IPAddress and count any other field you want. Sort descending on
counted field

Regards

Kevin
 
Try this --
SELECT HeuristicsDaily.[IPAddress], Count(HeuristicsDaily.[IPAddress]) AS
Multi_IP
FROM HeuristicsDaily
WHERE Count(HeuristicsDaily.[IPAddress]) >1
ORDER BY Count(HeuristicsDaily.[IPAddress]), HeuristicsDaily.[IPAddress];
 
Thanks Ken, that was perfect!

To include columns other than those on which the count is grouped try this:

SELECT IPAddress, Logged, Account, PIN, AddressCount
FROM
(SELECT *,
    (SELECT COUNT(*)
     FROM HeuristicsDaily As H2
     WHERE H2.IPAddress = H1.IPAddress)
AS AddressCount
FROM HeuristicsDaily AS H1)
WHERE AddressCount > 1
ORDER BY AddressCount DESC;

Ken Sheridan
Stafford, England

pand0ra.usa said:
I have a query and I am not sure how to go about counting the number
of duplicate IP Addresses and sorting on the most found (i.e. I want
to see the IP Address that has the most duplicates at the top of the
list).
Click to expand...
SELECT HeuristicsDaily.[IPAddress], HeuristicsDaily.[Logged],
HeuristicsDaily.[Account], HeuristicsDaily.[PIN]
FROM HeuristicsDaily
WHERE (((HeuristicsDaily.[IPAddress]) In (SELECT [IPAddress] FROM
[HeuristicsDaily] As Tmp GROUP BY [IPAddress] HAVING Count(*)>1 )))
ORDER BY HeuristicsDaily.[IPAddress];
Click to expand...
Click to expand...
 
Back
Top