Dudes,

How can I make a GROUP BY function into an SQL instruction, to count how much machines by ip address range (until the 3rd range)?

Ex: (Existent registers for a field called ip_addr)

172.17.17.1
172.17.17.2
172.17.17.3
172.18.196.12
172.18.196.22
172.22.3.1

Must returns:
Network 172.17.17: 3 machines
Network 172.18.196: 2 machines
Network 172.22.3: 1 machine

Dudes,

How can I make a GROUP BY function into an SQL instruction, to count how much machines by ip address range (until the 3rd range)?

Ex: (Existent registers for a field called ip_addr)

172.17.17.1
172.17.17.2
172.17.17.3
172.18.196.12
172.18.196.22
172.22.3.1

Must returns:
Network 172.17.17: 3 machines
Network 172.18.196: 2 machines
Network 172.22.3: 1 machine

You write a UDF that takes the whole IP address column and returns just the first 3 ranges. Then you query looks like: SELECT MyUDF(IPCOL),count(*) from MYTable group by MyUDF(IPCOL) ORDER BY 1

HTH

Andy

Answered in duplicate thread.

locate 3 location of '.'

select loc,count(*) from (select (substr(IP,1,locate('.',IP,(locate('.',IP,((locate ('.',IP))+1))+1)))) As loc from Test11 )As B
group by loc

LOC 2
--------------- -----------
172.17.17. 3
172.17.172. 1

I couldn't test this query. Would this work for you..?

SELECT SUBSTR(LOC,1,LOCATE('.',LOC,3)),COUNT(*) from tablename GROUP BY SUBSTR(LOC,1,LOCATE('.',LOC,3))

3 parameter in locate specifies the starting position from where to start search

values locate('r','rahulrrsingh',2)

1
-----------
6

values locate('r','rahulrrsingh',3)

1
-----------
6

"vini_srcna", sorry, but it doesn't work...

Result:

0.0. 2
0.1. 1
1.1. 3
10. 500
169. 11
172. 4312
192. 195
200. 16

"rahul_s80", your tip was simply perfect!

Thanks a lot, dude!

Thats great. Thanks for the update.