From: MaXX <bs139412@skynet.be>
To: pgsql-sql@postgresql.org
Subject: Re: Merging lines with NULLs (with example data)
Date: Thu, 27 Oct 2005 22:07:09 +0200
Message-ID: <djrc1q$td3$1@talisker.lacave.net> (raw)
References: <djdp5l$1l4f$1@talisker.lacave.net>
<87u0f5sofz.fsf@srv.protecting.net>
<435E3B23.1070909@brandywine.com>
Thank you, and sorry for the late answer, I was far away from a decent
internet connection...
I'll try both your solutions, EXPLAIN ANALYSE will elect the winner... In
any case that will be cleaner than my dirty hack (2 distinct queries) which
generate a lot of garbage...
Thanks again,
MaXX
Daryl Richter wrote:
> Harald Fuchs wrote:
>> 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. :)
--
MaXX
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: bs139412@skynet.be
Subject: Re: Merging lines with NULLs (with example data)
In-Reply-To: <djrc1q$td3$1@talisker.lacave.net>
* 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