Postgres Pro
Downloads
Home   >   mailing lists

assistance on self join pls - Mailing list pgsql-sql

From email lists
Subject assistance on self join pls
Date
Msg-id 035C9F7CE28601428BBB5B051C9F77F2017922@orion.mackay.local
Whole thread Raw
Responses Schemata & User-Defined-Type casting issues
Re: assistance on self join pls
List pgsql-sql
Tree view 
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 |           38812004-05-05 05:00:00 |        6 | 22326 | 192.168.11.191 |
205.227.137.53 |              1 |           25922004-05-05 05:00:00 |        6 | 38005 | 192.168.11.191 |
205.227.137.53 |              1 |          512862004-05-05 05:00:00 |        6 | 51861 | 192.168.11.191 |
205.227.137.53 |              1 |          424602004-05-05 05:00:00 |        6 | 52095 | 192.168.11.191 |
205.227.137.53 |              1 |           25582004-05-05 05:00:00 |        6 | 59846 | 192.168.11.191 |
205.227.137.53 |              1 |            1182004-05-05 05:00:00 |        6 | 60243 | 192.168.11.191 |
205.227.137.53 |              1 |           20922004-05-05 06:00:00 |        6 |    21 | 192.168.11.185 |
205.227.137.53 |              6 |           38142004-05-05 06:00:00 |        6 | 29799 | 192.168.11.185 |
205.227.137.53 |              1 |            1182004-05-05 06:00:00 |        6 | 30138 | 192.168.11.185 |
205.227.137.53 |              1 |           20922004-05-05 06:00:00 |        6 | 30215 | 192.168.11.185 |
205.227.137.53 |              1 |          424602004-05-05 06:00:00 |        6 | 51279 | 192.168.11.185 |
205.227.137.53 |              1 |           13322004-05-05 06:00:00 |        6 | 52243 | 192.168.11.185 |
205.227.137.53 |              1 |          512862004-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 |         1049872004-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

pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: insert or update violates foreign key constraint.why?
Next
From: Bruno Wolff III
Date:
Subject: Re: sum ( time) problem