From: Harald Fuchs <hf0923x@protecting.net>
To: pgsql-sql@postgresql.org
Subject: Re: Merging lines with NULLs (with example data)
Date: 25 Oct 2005 15:42:24 +0200
Message-ID: <87u0f5sofz.fsf@srv.protecting.net> (raw)
References: <djdp5l$1l4f$1@talisker.lacave.net>
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
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: hf0923x@protecting.net
Subject: Re: Merging lines with NULLs (with example data)
In-Reply-To: <87u0f5sofz.fsf@srv.protecting.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