From: MaXX <bs139412@skynet.be>
Subject: Merging lines with NULLs (with example data)
Date: Sat, 22 Oct 2005 18:25:17 +0200
Organization: Service de news de lacave.net
Lines: 99
Message-ID: <djdp5l$1l4f$1@talisker.lacave.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
User-Agent: KNode/0.9.2
To: pgsql-sql@postgresql.org

Good afternoon,

I have a simple problem, and I feel stupid not finding myself what's the
solution... I try to explain shortly, but as I'm not really confident with
my explanation, I provided a complete example with data below.

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

Thanks in advance,
MaXX

Here's all the details:
I have a table
CREATE TABLE test
(
  id serial NOT NULL,
  tstamp timestamptz,
  host varchar(80),
  rulenr int4,
  act varchar(10),
  proto varchar(4),
  src_ip inet,
  src_port int4,
  dst_ip inet,
  dst_port int4,
  dir varchar(3),
  if varchar(5),
  reported bool,
  protected bool,
  CONSTRAINT pk_ipfw_id PRIMARY KEY (id)
) 
WITH OIDS;

Data:
INSERT INTO test VALUES (453639,'2005-10-21
09:39:19+02','akar',600,'Deny','UDP','10.182.144.167',52616,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (453634,'2005-10-21
09:36:21+02','akar',600,'Deny','UDP','10.182.144.167',52616,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (453633,'2005-10-21
09:36:20+02','akar',600,'Deny','UDP','10.182.144.167',52616,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (452610,'2005-10-21
03:33:28+02','akar',600,'Deny','TCP','10.182.174.7',4310,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (451735,'2005-10-21
00:11:52+02','akar',600,'Deny','UDP','10.216.48.231',6778,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (451734,'2005-10-21
00:11:50+02','akar',600,'Deny','UDP','10.216.48.231',6778,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (448277,'2005-10-20
16:31:17+02','akar',600,'Deny','UDP','10.244.165.152',31262,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (448276,'2005-10-20
16:31:15+02','akar',600,'Deny','UDP','10.244.165.152',31262,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (448266,'2005-10-20
16:29:08+02','akar',600,'Deny','TCP','10.224.32.85',63891,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (448265,'2005-10-20
16:29:05+02','akar',600,'Deny','TCP','10.224.32.85',63891,'10.0.0.1',2290,'in','rl0',TRUE,NULL);
INSERT INTO test VALUES (448258,'2005-10-20
16:28:16+02','akar',600,'Deny','UDP','10.244.165.152',31262,'10.0.0.1',2290,'in','rl0',TRUE,NULL);

when I execute a query like this,
select  to_date(tstamp,'YYYY-MM-DD')as gday,
        case when proto='UDP'
                then count(id)
                else NULL
             end as count_udp,
        case when proto='TCP'
                then count(id)
                else NULL
             end as count_tcp
from test 
        where tstamp >= (now() - interval '$days days' )
        and dst_port = $port
        group by gday, proto
        order by gday;
I get:
gday,count_udp,count_tcp
'2005-10-20','','2'
'2005-10-20','3',''
'2005-10-21','','1'
'2005-10-21','5',''

This is not what I want, I want that:
gday,count_udp,count_tcp
'2005-10-20','3','2'
'2005-10-21','5','1'

-- 
MaXX