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