Re: Merging lines with NULLs (with example data)

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