SELECT `registrationid` as `schoolid`, `LName` as `schoolname`, `address`, `city`, `state`, `educationboard`, `profiletype`, `gender`, `schoolcategory`, `localityarea`, (select count(*) from tblreviews where tblreviews.sourceid = tblregistration.registrationid) as reviewcount, (select avg(rating) from tblreviews where tblreviews.sourceid = tblregistration.registrationid) as rating, (select count(*) from tblschoolaccount where tblschoolaccount.schoolid = RegistrationId) as isclaimed, (select objectid from tblcompareobject where tblcompareobject.objectid=tblregistration.RegistrationId AND isdelete = 0 AND tblcompareobject.usersession= '9nea7spgdmc6223lm3molgaeucoqcvhu' ) as cmpid, (select count(*) from tblcompareobject where tblcompareobject.objectid=tblregistration.RegistrationId) as comparecount, (select count(*) FROM `tblschoolviews` where tblschoolviews.schoolid = tblregistration.RegistrationId) as viewcount FROM `tblregistration` WHERE `city` = 'New delhi' AND `LocalityArea` like '%pitampura%' ORDER BY `reviewcount` desc LIMIT 10