calculate average rating in sql server -

this table:

i want fetch records of vendor contain servicedescription "plaster" or skills "plaster" or in category "plaster" , want calculate averagerating of vendor.

note:if there no review of vendor records should come.

this query:

select * userdetails u   ,vendorincategory v   ,categorymaster c   ,review rv v.categoryid=c.id  , u.id=r.userid  , u.id=rv.vendorid   , v.vendorid=u.id  , ((u.servicedescription '%plaster%' )  or (u.skills '%plaster%')  or (c.name '%plaster%')) 

here problem in above query not getting vendor review not there.

but want vendor not contain review matches criteria.

userdetails:

id     servicedescription         skills 1        plaster                  plaster  2        construction             construvtion  3        plaster                  plaster  4        null                     null(not vendor)  5        null                     null(not vendor) 

review

id     customerid     vendorid    rating  1       4                1          3  2       5                1          3 

expected output:

vendorid     servicedescription     skills       averagerating  1              plaster              plaster           3  3              plaster              plaster           0 

note:final output should in descending order of average rating

here, try this:

sample data

create table userdetails(     id int,     servicedescription varchar(20),     skills varchar(20) ) create table review(     id int,     customerid int,     vendorid int,     rating int )  insert userdetails values(1, 'plaster', 'plaster'),(2, 'construction', 'construction'),(3, 'plaster', 'plaster'); insert review values(1, 4, 1, 3),(2, 5, 1, 3); 

solution

select     u.id vendorid,     u.servicedescription,     u.skills,     isnull(sum(r.rating)/count(r.rating), 0) averagerating userdetails u left join review r     on r.vendorid = u.id     u.servicedescription '%plaster%'     or u.skills '%plaster%' group      u.id,     u.servicedescription,     u.skills order averagerating desc