X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 87EDED1B18C for ; Wed, 2 Jun 2004 10:48:32 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 37641-08 for ; Wed, 2 Jun 2004 13:48:35 +0000 (GMT) Received: from trade-india.com (infocom02.trade-india.com [67.18.55.69]) by svr1.postgresql.org (Postfix) with SMTP id 7089ED1B173 for ; Wed, 2 Jun 2004 10:48:30 -0300 (ADT) Received: (qmail 7016 invoked from network); 2 Jun 2004 13:50:40 -0000 Received: from unknown (HELO ?203.145.130.142?) (203.145.130.142) by infocom02.trade-india.com with SMTP; 2 Jun 2004 13:50:40 -0000 Message-ID: <40BDDAA1.1040903@trade-india.com> Date: Wed, 02 Jun 2004 19:18:17 +0530 From: Rajesh Kumar Mallah User-Agent: Mozilla Thunderbird 0.6 (X11/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: email lists Cc: pgsql-sql@postgresql.org Subject: Re: assistance on self join pls References: <035C9F7CE28601428BBB5B051C9F77F2017922@orion.mackay.local> In-Reply-To: <035C9F7CE28601428BBB5B051C9F77F2017922@orion.mackay.local> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/13 X-Sequence-Number: 17958 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) > > >