CODEWITHSUNDEEP
sqlpostgresqlselectsum
Foad Tahmasebi
Foad Tahmasebi

Reputation: 1352

How to find top usage of IP pairs in PostgreSQL?

I have a table with source_ip and destination_ip and usage. now i want to find top usage for ip pairs. My table is like this:

source_ip     | destination_ip | usage
192.168.1.1   | 192.168.1.2    | 20
192.168.1.2   | 192.168.1.1    | 30
192.168.1.3   | 192.168.1.2    | 20
192.168.1.2   | 192.168.1.3    | 20

For example this two records are one pair.

source_ip   | destination_ip | usage
192.168.1.1 | 192.168.1.2    | 20
192.168.1.2 | 192.168.1.1    | 30

finally I want to get this

192.168.1.1 and 192.168.1.2 used 50 b
192.168.1.2 and 192.168.1.3 used 40 b

this is my query, 

with T1 as(
    SELECT  source_ip, distination_ip, sum(usage)
    FROM receiver
    GROUP BY source_ip, distination_ip
)
SELECT DISTINCT * FROM T1 JOIN T1 T2
ON T1.source_ip = T2.distination_ip AND T1.distination_ip = T2.source_ip

and my query return this:

source_ip     | destination_ip | usage | source_ip     | destination_ip | usage
192.168.1.1   | 192.168.1.2    | 20    | 192.168.1.2   | 192.168.1.1    | 30
192.168.1.2   | 192.168.1.1    | 30    | 192.168.1.1   | 192.168.1.2    | 20
192.168.1.3   | 192.168.1.2    | 20    | 192.168.1.2   | 192.168.1.3    | 20
192.168.1.2   | 192.168.1.3    | 20    | 192.168.1.3   | 192.168.1.2    | 20

Upvotes: 1

Views: 52

Answers (2)

jarlh
jarlh

Reputation: 44776

Use a derived table to "sort" ip-addresses. The GROUP BY and SUM it's result.

select ip1, ip2, sum(usage)
from
(
select case when source_ip < destination_ip then source_ip else destination_ip end ip1,
       case when source_ip > destination_ip then source_ip else destination_ip end ip2,
       usage
from receiver
)
group by ip1, ip2

Upvotes: 2

Mureinik
Mureinik

Reputation: 311508

You can make an arbitrary decision to represent the the "smaller" IP address first and the "larger" second. From there on, it's a simple group by and sum:

WITH t1 AS (
    SELECT  LEAST(source_ip, distination_ip) AS ip1,
            GREATEST(source_ip, distination_ip) AS ip2 , 
            usage
    FROM    receiver
)
SELECT   ip1, ip2, SUM(usage)
FROM     t1
GROUP BY ip1, ip2

Or, if you want the result formatting in the query itself:

WITH t1 AS (
    SELECT  LEAST(source_ip, distination_ip) AS ip1,
            GREATEST(source_ip, distination_ip) AS ip2 , 
            usage
    FROM    receiver
)
SELECT   ip1 || ' and ' || ip2 || ' used ' || SUM(usage) || ' b'
FROM     t1
GROUP BY ip1, ip2

Upvotes: 3

Related Questions