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