Count IP Addresses

[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];

 

[kc-mass]

Group on IPAddress and count any other field you want. Sort descending on
counted field

Regards

Kevin

 

[KARL DEWEY]

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];

 

[pand0ra.usa]

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

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];