SELECT id count mac sum FROM snmp_ports LEFT OUTER JOIN SELECT id mac

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT s.id, count(z.mac), sum(z.c) FROM snmp_ports s
LEFT OUTER JOIN
(
SELECT s.id, f.mac, case when count(a.mac)>0 then 1 else 0 end as c from snmp_ports s
LEFT OUTER jOIN fdb f on (f.port = s.id AND f.last_seen > now()-'30 days'::interval)
left outer join arp a on (f.mac = a.mac)
where s.equipment=3097
group by s.id, f.mac) as z ON s.id=z.id
where s.equipment = 3097
group by s.id order by s.id