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
SELECT FLOOR(ipAsInteger/256) AS ipDiv256, COUNT(*) AS howmany
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:
SELECT LEFT( ipaddress, LENGTH(ipaddress) - INSTR(REVERSE(ipaddress),'.') ) AS left3, COUNT(*) AS howmany
GROUP BY LEFT( ipaddress, LENGTH(ipaddress) - INSTR(REVERSE(ipaddress),'.') )
ORDER BY 1
You see it?
REVERSE of (example) '188.8.131.52' is '73.98.981.99'
INSTR( '73.98.981.99', '.' ) is 3
LENGTH('184.108.40.206') is 12
12 - 3 is 9
LEFT('220.127.116.11',9) is '99.198.98'
REVERSE of (example) '18.104.22.168' is '122.98.981.99'
INSTR( '122.98.981.99', '.' ) is 4
LENGTH('22.214.171.124') is 13
13 - 4 is 9
LEFT('126.96.36.199',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)
will come before
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.