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= '2ecqhsssbiuj65f7dm9s7hgtsj7bnq94' ) 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` = 'Hyderabad' AND `LocalityArea` like '%uppal%' ORDER BY `reviewcount` desc LIMIT 10