CODEWITHSUNDEEP
sqlpostgresqlip-addressaggregation
Luis Henrique
Luis Henrique

Reputation: 771

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

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

Upvotes: 3

Views: 1163

Answers (3)

Ashraful Islam
Ashraful Islam

Reputation: 47

How to get the first two octets in an IP address Oracle SQL?

Regex solution : SELECT REGEXP_SUBSTR("youField", '[0-9]+.[0-9]+.') AS "output"

Reference URL: https://dba.stackexchange.com/questions/173277/how-to-get-the-first-two-octets-in-an-ip-address-oracle-sql

How to get the first three octets in an IP address Oracle SQL? It's working for me.

select REGEXP_SUBSTR(YOUR_IP_FIELD, '[0-9]+.[0-9]+.[0-9]+') as IP_Block, count(*) as Number_Of_IP from YOUR_TABLE group by REGEXP_SUBSTR(YOUR_IP_FIELD, '[0-9]+.[0-9]+.[0-9]+')

Upvotes: 0

Schwern
Schwern

Reputation: 164919

Gordon covered doing this as strings. Storing and processing IP addresses as strings makes this and other processes slow and difficult and obtuse.

Instead, store them as cidr and use set_masklen to group addresses by their /24.

select
  set_masklen(netaddr, 24) as subnet,
  count(*)
from mytable
group by subnet

Though it may not benefit this particular query, cidr can be indexed using gist with inet_ops to benefit other queries.

create index on mytable using gist(netaddr inet_ops);

If you can't change the column type, you can still make use of the cidr functions with casting. set_masklen(netaddr::cidr, 24)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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

Upvotes: 2

Related Questions