Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Dec 2009
    Location
    Hong Kong
    Posts
    119
    Thanks
    8
    Thanked 0 Times in 0 Posts

    how to group and count with wildcards ?

    I have a table with IP numbers and like to group and count them in 255er lots.

    Example:
    Code:
    // data in table
    
    qtt	ip
    1	99.198.98.183
    5	99.198.127.42
    1	99.198.127.34
    3	99.198.125.46
    8	99.198.119.90
    1	99.198.119.182
    
    // wanted output:
    
    1	99.198.98.*
    6	99.198.127.*
    3	99.198.125.*
    9	99.198.119.*
    Can that be done or do I need PHP to help?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    A couple of ways it could be done. The best way, from a performance perspective, would probably be to add another field to the table, which is the UNSIGNED INT version of the IP address. You could calculate that (e.g, in PHP code) at the same time you insert the text version.

    And then you would just do
    Code:
    SELECT FLOOR(ipAsInteger/256) AS ipDiv256, COUNT(*) AS howmany
    FROM table
    GROUP BY FLOOR(ipAsInteger/256) 
    ORDER BY 1
    That will be faster than playing with the string form.

    But if you don't do this often, you could certainly do it from the string form:
    Code:
    SELECT LEFT( ipaddress, LENGTH(ipaddress) - INSTR(REVERSE(ipaddress),'.') ) AS left3, COUNT(*) AS howmany
    FROM table 
    GROUP BY LEFT( ipaddress, LENGTH(ipaddress) - INSTR(REVERSE(ipaddress),'.') ) 
    ORDER BY 1
    You see it?

    REVERSE of (example) '99.198.98.37' is '73.98.981.99'
    INSTR( '73.98.981.99', '.' ) is 3
    LENGTH('99.198.98.37') is 12
    12 - 3 is 9
    LEFT('99.198.98.37',9) is '99.198.98'

    REVERSE of (example) '99.198.98.221' is '122.98.981.99'
    INSTR( '122.98.981.99', '.' ) is 4
    LENGTH('99.198.98.221') is 13
    13 - 4 is 9
    LEFT('99.198.98.221',9) is '99.198.98'

    and so on.

    Fair warning: If you do ORDER BY 1 (that is, ORDER BY the first 3 groups of the ip address), you *WILL* find that (for example)
    122.1.1
    will come before
    2.255.255
    just for example. Because it will be *string* ordering, not numeric ordering. This is another reason you might consider adding an ipAsInteger field to your table.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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