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 a.locked='no' AND p.locked='no' AND c.name='Karachi'
GROUP BY a.id ORDER BY total DESC limit 0,6
