Message-ID: <435E3B23.1070909@brandywine.com>
Date: Tue, 25 Oct 2005 10:03:15 -0400
From: Daryl Richter <daryl@brandywine.com>
Reply-To: daryl@brandywine.com
Organization: Brandywine Asset Management
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
Cc: pgsql-sql@postgresql.org
Subject: Re: Merging lines with NULLs (with example data)
References: <djdp5l$1l4f$1@talisker.lacave.net>
	<87u0f5sofz.fsf@srv.protecting.net>
In-Reply-To: <87u0f5sofz.fsf@srv.protecting.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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