pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedassistance on self join pls
2+ messages / 2 participants
[nested] [flat]
* assistance on self join pls
@ 2004-06-01 12:29 email lists <lists@darrenmackay.com>
0 siblings, 1 reply; 2+ messages in thread
From: email lists @ 2004-06-01 12:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: assistance on self join pls
@ 2004-06-02 13:48 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: email lists <lists@darrenmackay.com>
0 siblings, 0 replies; 2+ messages in thread
From: Rajesh Kumar Mallah @ 2004-06-02 13:48 UTC (permalink / raw)
To: email lists <lists@darrenmackay.com>; +Cc: 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)
>
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-06-02 13:48 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-01 12:29 assistance on self join pls email lists <lists@darrenmackay.com>
2004-06-02 13:48 ` Rajesh Kumar Mallah <mallah@trade-india.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox