Hi, I tried this and it seems to work: SELECT SUBSTRING_INDEX(INET_NTOA(ip), '.',3) AS addr, COUNT(*) AS addrsum FROM ips GROUP BY addr;
Perhaps you don't have your IP adresses stored as integers but instead as text. Then the following query is for you! Your query: SELECT ip, COUNT(*) from tbl_MasterIp group by ip; Would then be something like this: SELECT SUBSTRING_INDEX(ip, '.', 3) AS ip, COUNT(*) AS addrsum FROM tbl_MasterIp Group By ip; Good luck, Mike On Monday 08 December 2003 15.23, chad kellerman wrote: > Hello everyone, > > I have a mysql table tha holds a variety of ip address. I am trying > to work out a select statement that will give me a count(*) grouped by > the first 2 octets of the ip. > > if the ip table contains: > > 10.102.1.1 > 192.168.123.34 > 192.168.123.4 > 192.168.123.43 > 192.168.123.3 > 192.168.0.27 > 192.168.0.212 > 10.102.1.54 > 10.102.1.75 > 10.102.1.62 > 10.102.1.12 > 10.102.1.10 > 10.102.1.111 > > how can my: > SELECT ip, COUNT(*) from tbl_MasterIp group by ip; > > be changed so that I get back > > ip COUNT(*) > 10.102.1 20 > 192.168.123 30 > 192.168.0 10 > > or whatever the ip counts should be. I have about 5 different octets > within this table. > > I am using mysql 3.* > > Thanks for any help... > > > > Sincerely, > Chad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
