Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2007
    Posts
    11

    Question Unanswered: How to create a GROUP BY for use with ip address range?

    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

  2. #2
    Join Date
    Mar 2007
    Posts
    11

    Question How to create a GROUP BY for use with ip address range?

    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

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Answered in duplicate thread.

  5. #5
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  6. #6
    Join Date
    May 2006
    Posts
    82
    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))
    Vinay,

  7. #7
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  8. #8
    Join Date
    Mar 2007
    Posts
    11
    "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

  9. #9
    Join Date
    Mar 2007
    Posts
    11
    "rahul_s80", your tip was simply perfect!

    Thanks a lot, dude!

  10. #10
    Join Date
    May 2006
    Posts
    82
    Thats great. Thanks for the update.
    Vinay,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •