calculate average rating in sql server

Question

this is my table:

I want to fetch records of Those Vendor which contain ServiceDescription "Plaster" or Skills "Plaster" or is in Category "Plaster" and also want to calculate averagerating of those Vendor.

note:If there is no review Of any vendor then also that records should come.

this is my query:

select * from UserDetails u
  ,VendorInCategory v
  ,CategoryMaster c
  ,Review rv
where v.CategoryId=c.Id 
and u.Id=r.UserId 
and u.Id=rv.VendorId  
and v.VendorId=u.Id 
and ((u.ServiceDescription like '%Plaster%' ) 
or (u.Skills like '%Plaster%') 
or (c.Name like '%Plaster%'))

here problem in above query is i am not getting that vendor whose review is not there.

but i also want that vendor which does not contain review but matches my 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

Answer 1

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 into UserDetails values(1, 'Plaster', 'plaster'),(2, 'construction', 'construction'),(3, 'plaster', 'plaster');
insert into Review values(1, 4, 1, 3),(2, 5, 1, 3);

SOLUTION

select
    u.Id as VendorId,
    u.ServiceDescription,
    u.Skills,
    isnull(sum(r.rating)/count(r.rating), 0) as AverageRating
from UserDetails u
left join Review r
    on r.VendorId = u.id
where
    u.ServiceDescription like '%plaster%'
    or u.Skills like '%plaster%'
group by 
    u.Id,
    u.ServiceDescription,
    u.Skills
order by AverageRating desc