Re: assistance on self join pls - Mailing list pgsql-sql
| From | Rajesh Kumar Mallah |
|---|---|
| Subject | Re: assistance on self join pls |
| Date | |
| Msg-id | 40BDDAA1.1040903@trade-india.com Whole thread Raw |
| In response to | assistance on self join pls ("email lists" <lists@darrenmackay.com>) |
| List | pgsql-sql |
Dear Darren, Your question is not very clear to me. On what columns do you want to aggregate? suppose u want to aggregate on outsite and inside ip you shud group by those columns and run a aggregate function like sum or avg etc , suppose u want the total traffic for every pair you can do this: select inside_ip,outside_ip , sum(outbound_bytes) as total_traffic from connection_data group by inside_ip,outside_ip ; Hope it helps. Regds Mallah. email lists wrote: >Hi all, > >I have the following firewall connection data. > > datetime | protocol | port | inside_ip | outside_ip >| outbound_count | outbound_bytes >---------------------+----------+-------+----------------+-------------- >--+----------------+--------------- > 2004-05-05 05:00:00 | 6 | 21 | 192.168.11.191 | >205.227.137.53 | 6 | 3881 > 2004-05-05 05:00:00 | 6 | 22326 | 192.168.11.191 | >205.227.137.53 | 1 | 2592 > 2004-05-05 05:00:00 | 6 | 38005 | 192.168.11.191 | >205.227.137.53 | 1 | 51286 > 2004-05-05 05:00:00 | 6 | 51861 | 192.168.11.191 | >205.227.137.53 | 1 | 42460 > 2004-05-05 05:00:00 | 6 | 52095 | 192.168.11.191 | >205.227.137.53 | 1 | 2558 > 2004-05-05 05:00:00 | 6 | 59846 | 192.168.11.191 | >205.227.137.53 | 1 | 118 > 2004-05-05 05:00:00 | 6 | 60243 | 192.168.11.191 | >205.227.137.53 | 1 | 2092 > 2004-05-05 06:00:00 | 6 | 21 | 192.168.11.185 | >205.227.137.53 | 6 | 3814 > 2004-05-05 06:00:00 | 6 | 29799 | 192.168.11.185 | >205.227.137.53 | 1 | 118 > 2004-05-05 06:00:00 | 6 | 30138 | 192.168.11.185 | >205.227.137.53 | 1 | 2092 > 2004-05-05 06:00:00 | 6 | 30215 | 192.168.11.185 | >205.227.137.53 | 1 | 42460 > 2004-05-05 06:00:00 | 6 | 51279 | 192.168.11.185 | >205.227.137.53 | 1 | 1332 > 2004-05-05 06:00:00 | 6 | 52243 | 192.168.11.185 | >205.227.137.53 | 1 | 51286 > 2004-05-05 06:00:00 | 6 | 60079 | 192.168.11.185 | >205.227.137.53 | 1 | 2558 > >I am wanting to aggregate / collapse each entry to something similar to: > > datetime | protocol | port | inside_ip | outside_ip >| outbound_count | outbound_bytes >---------------------+----------+-------+----------------+-------------- >--+----------------+--------------- > 2004-05-05 05:00:00 | 6 | 21 | 192.168.11.191 | >205.227.137.53 | 12 | 104987 > 2004-05-05 06:00:00 | 6 | 21 | 192.168.11.185 | >205.227.137.53 | 12 | 103660 > >I have not had much success - any assistance greatly appreciated > >Darren > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > >