How to group IP list by subnet in MySQL?

group-by, ip, mysql, subnet

Solution

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.

Problem

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?

Original source