How to use "group by" to group data by the IP

Question

How to use group by to group data by the IP range in postgres?

My table:

   "NETADDR"    |
----------------|
 192.168.13.6   |   
 192.168.13.6   |
 192.168.14.5   |
 192.168.14.6   |

Desired output:

   "NETADDR"    |    "COUNT"
----------------|----------------|
 192.168.13     |      2
 192.168.14     |      2

Answer 1

I'm not sure if cidr would make this that much more efficient, because you still need to aggregate. You can construct the Type C address using array functions:

select array_to_string((regexp_split_to_array(netaddr, '\.'))[1:3], '.') as typec,
       count(*)
from t
group by typec;

Or using regular expressions:

select regexp_replace(netaddr, '(^[0-9]+[.][0-9]+[.]+[0-9]).*$', '\1') as typec,
       count(*)
from t
group by typec