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…

Accepted Answer

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
```

calculate average rating in sql server

Solution

Problem