Re: Merging lines with NULLs (with example data) - Mailing list pgsql-sql

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'
ANDdst_port = $port GROUP BY gday ORDER BY gday