Harald Fuchs wrote:
In article <[EMAIL PROTECTED]>,
MaXX <[EMAIL PROTECTED]> 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
Or, via a subquery:
select distinct to_date(tstamp,'YYYY-MM-DD') as gday,
( select count(id) from test t1 where proto='UDP' and
to_date(t1.tstamp,'YYYY-MM-DD') = to_date(test.tstamp,'YYYY-MM-DD') ) as
count_udp,
( select count(id) from test t1 where proto='TCP' and
to_date(t1.tstamp,'YYYY-MM-DD') = to_date(test.tstamp,'YYYY-MM-DD') ) as
count_tcp
from test
where tstamp >= (now() - interval '6 days' )
and dst_port = 2290
order by gday;
Harald's solution is better for your particular case and will almost
certainly be faster, but subqueries are good to know how to do. :)
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
--
Daryl Richter
Platform Author & Director of Technology
(( Brandywine Asset Management )
( "Expanding the Science of Global Investing" )
( http://www.brandywine.com ))
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
