Re: Merging lines with NULLs (with example data)

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          ))