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 a.type='agency' AND c.name='Islamabad'
GROUP BY a.id ORDER BY total DESC limit 0,6
