From: Daryl Richter <daryl@brandywine.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Merging lines with NULLs (with example data)
Date: Tue, 25 Oct 2005 10:03:15 -0400
Message-ID: <435E3B23.1070909@brandywine.com> (raw)
In-Reply-To: <87u0f5sofz.fsf@srv.protecting.net>
References: <djdp5l$1l4f$1@talisker.lacave.net>
<87u0f5sofz.fsf@srv.protecting.net>
Harald Fuchs wrote:
> In article <djdp5l$1l4f$1@talisker.lacave.net>,
> MaXX <bs139412@skynet.be> writes:
>
>
>>How can I "merge" this
>>gday,count_udp,count_tcp
>>'2005-10-20','','2'
>>'2005-10-20','3',''
>>'2005-10-21','','1'
>>'2005-10-21','5',''
>
>
>>into that:
>>gday,count_udp,count_tcp
>>'2005-10-20','3','2'
>>'2005-10-21','5','1'
>
>
>>in a single query???
>
>
> Try something like that:
>
> SELECT to_date (tstamp,'YYYY-MM-DD') AS gday,
> sum(CASE WHEN proto = 'UDP' THEN 1 ELSE 0 END) AS count_udp,
> sum(CASE WHEN proto = 'TCP' THEN 1 ELSE 0 END) AS count_tcp
> FROM test
> WHERE tstamp >= now() - INTERVAL '$days DAYS'
> AND dst_port = $port
> GROUP BY gday
> ORDER BY gday
>
Or, via a subquery:
select distinct to_date(tstamp,'YYYY-MM-DD') as gday,
( select count(id) from test t1 where proto='UDP' and
to_date(t1.tstamp,'YYYY-MM-DD') = to_date(test.tstamp,'YYYY-MM-DD') ) as
count_udp,
( select count(id) from test t1 where proto='TCP' and
to_date(t1.tstamp,'YYYY-MM-DD') = to_date(test.tstamp,'YYYY-MM-DD') ) as
count_tcp
from test
where tstamp >= (now() - interval '6 days' )
and dst_port = 2290
order by gday;
Harald's solution is better for your particular case and will almost
certainly be faster, but subqueries are good to know how to do. :)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Daryl Richter
Platform Author & Director of Technology
(( Brandywine Asset Management )
( "Expanding the Science of Global Investing" )
( http://www.brandywine.com ))
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: daryl@brandywine.com
Subject: Re: Merging lines with NULLs (with example data)
In-Reply-To: <435E3B23.1070909@brandywine.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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