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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
