X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 54AA7DAD43
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 25 Oct 2005 10:52:06 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 35357-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 25 Oct 2005 13:52:02 +0000 (GMT)
Received: from ciao.gmane.org (main.gmane.org [80.91.229.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 73535DACB4
	for <pgsql-sql@postgresql.org>; Tue, 25 Oct 2005 10:52:04 -0300 (ADT)
Received: from root by ciao.gmane.org with local (Exim 4.43)
	id 1EUPCI-0007Pa-CK
	for pgsql-sql@postgresql.org; Tue, 25 Oct 2005 15:50:38 +0200
Received: from srv.protecting.net ([212.126.218.242])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Tue, 25 Oct 2005 15:50:38 +0200
Received: from hf0923x by srv.protecting.net with local (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Tue, 25 Oct 2005 15:50:38 +0200
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-sql@postgresql.org
From: Harald Fuchs <hf0923x@protecting.net>
Subject: Re: Merging lines with NULLs (with example data)
Date: 25 Oct 2005 15:42:24 +0200
Organization: Linux Private Site
Lines: 27
Message-ID: <87u0f5sofz.fsf@srv.protecting.net>
References: <djdp5l$1l4f$1@talisker.lacave.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
X-Complaints-To: usenet@sea.gmane.org
X-Gmane-NNTP-Posting-Host: srv.protecting.net
User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/20.7
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.103 required=5 tests=[AWL=-0.103,
	UPPERCASE_25_50=0.207]
X-Spam-Level: 
X-Archive-Number: 200510/285
X-Sequence-Number: 23138

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