Merging lines with NULLs (with example data)

From: MaXX <bs139412(at)skynet(dot)be>
To: pgsql-sql(at)postgresql(dot)org
Subject: Merging lines with NULLs (with example data)
Date: 2005-10-22 16:25:17
Message-ID: djdp5l$1l4f$1@talisker.lacave.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Nicolas Cornu 2005-10-24 09:59:56 unsuscribe
Previous Message Dean Gibson (DB Administrator) 2005-10-22 15:16:48 Re: Blank-padding