SELECT a.*,count(p.id) as total,c.name as city_name
FROM agency a
LEFT JOIN properties p ON a.id=p.agency_id AND p.property_status<>'rejected'
LEFT JOIN cities c ON c.id=a.city
WHERE (p.property_status='live' OR p.property_status='verified') AND a.locked='no' AND p.locked='no' AND a.city=246
GROUP BY a.id ORDER BY total DESC limit 0,6
