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= 'l6ca7rbf0fp8gqhserfnqt858qga5ds0' ) 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` = 'Ludhiana' AND `LocalityArea` like '%jagraon%' ORDER BY `reviewcount` desc LIMIT 10