How to group IP list by subnet in MySQL?

Question

I need to group IP list by subnet (first 3 octets) to count it. For example, if I have Ips

123.12.12.12
123.12.12.11
123.12.11.11

I have to get such result:

123.12.12 | 2
123.12.11 | 1

I googled this expample:

select 
substr(ip,1,locate('.',ip,locate('.',ip)+1)-1) 
as ip, count(ip) as count
from ip_list
group by ip ORDER BY count  DESC

But it groups the list only by first two octets. I was lost in all these locate(locate(locate(...))). Can somebody help to modify this to get proper results?

Answer 1

You should have used group by expression name.

select 
--   locate( '.', ip, locate( '.', ip, locate( '.', ip ) + 1 ) + 1 ) as l,
   substr( ip, 1, locate( '.', ip
                          , locate( '.', ip
                                    , locate( '.', ip ) + 1 ) + 1 ) - 1 ) as subip,
   count(ip) as count
from ip_list
group by ( subip )
order by count desc
;

EDIT 1:
Use of locate is not required. SUBSTR_INDEX can be used to filter the subset of IP's.

Example:

select
       substring_index( ip, '.', 3 ) as subip
     , count(ip) as count
  from ip_list
 group by ( subip )
 order by count desc

Refer to Documentation:

• MySQL: SUBSTR(str,pos,len)
  • Return a substring len characters long from string str, starting at position pos.
• MySQL: SUBSTRING_INDEX(str,delim,count)
  • Returns the substring from string str before count occurrences of the delimiter delim.

Answer 2

Using this logic I'm afraid you'll be needing another layer of locate there:

substr(ip, 1, locate('.', ip, locate('.', ip, locate('.', ip)+1) +1) - 1)