In article <djdp5l$1l4f$1(at)talisker(dot)lacave(dot)net>,
MaXX <bs139412(at)skynet(dot)be> 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