X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 4E853D1B1AB for ; Tue, 1 Jun 2004 09:28:53 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 27982-03 for ; Tue, 1 Jun 2004 09:28:54 -0300 (ADT) Received: from gizmo10bw.bigpond.com (gizmo10bw.bigpond.com [144.140.70.20]) by svr1.postgresql.org (Postfix) with SMTP id A9F29D1B1AC for ; Tue, 1 Jun 2004 09:28:47 -0300 (ADT) Received: (qmail 2378 invoked from network); 1 Jun 2004 12:22:21 -0000 Received: from unknown (HELO bwmam07.bigpond.com) (144.135.24.87) by gizmo10bw.bigpond.com with SMTP; 1 Jun 2004 12:22:21 -0000 Received: from cpe-203-45-129-139.qld.bigpond.net.au ([203.45.129.139]) by bwmam07.bigpond.com(MAM REL_3_4_2a 62/5868392) with SMTP id 5868392; Tue, 01 Jun 2004 22:28:48 +1000 Received: from [192.168.1.203] (helo=orion.mackay.local) by www.darrenmackay.com with esmtp (Exim 4.33) id 1BV8Ns-00046L-Bs for pgsql-sql@postgresql.org; Tue, 01 Jun 2004 22:28:48 +1000 Received: from orion.mackay.local (unverified) by orion.mackay.local (Content Technologies SMTPRS 4.3.12) with ESMTP id for ; Tue, 1 Jun 2004 22:29:04 +1000 Subject: assistance on self join pls MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Date: Tue, 1 Jun 2004 22:29:04 +1000 X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 content-class: urn:content-classes:message Message-ID: <035C9F7CE28601428BBB5B051C9F77F2017922@orion.mackay.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: assistance on self join pls Thread-Index: AcRH1AUKCrCMIWg7QDOiQWY0fecTeg== From: "email lists" To: X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200406/1 X-Sequence-Number: 17946 Hi all, I have the following firewall connection data.=20 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