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

Answer 2

Many users have used the AVERAGE function to calculate the average of a series of data. But what do you do when you have summary data instead of individual responses and need to calculate an average? (For example, counts of the number of people who selected each rating on a 5-point rating scale like the rating of a product.)

How to Calculate a Weighted Average

Let’s say you want to get the average overall rating for each product:

• For rating 1, (9) nine people.
• For rating 2, (13) Thirteen people.
• For rating 3, (1) one people.

Using the AVERAGE function would result in an average of 7.7. Of course, this doesn’t make any sense. We should expect an average within the range of the scale (1 to 5).

In order to correctly calculate the average overall response to each question, we need to:

1. Multiply the number of individuals selecting each rating by the corresponding rating value (1 – 5)
2. Add the results of those calculations together.
3. Divide that result by the total number of responses to the question.

SAMPLE DATA:

create table #tableRatings(
    Id int,
    Rating numeric(18,6)
)

insert into #tableRatings values(1, 4.3),(2,3.3),(3,4.8);

SOLUTION:

    SELECT
    SUM(
    case 
        WHEN FLOOR(rating) = 1 THEN rating
        WHEN FLOOR(rating) = 2 THEN rating *2
        WHEN FLOOR(rating) = 3 THEN rating *3
        WHEN FLOOR(rating) = 4 THEN rating *4
        WHEN FLOOR(rating) = 5 THEN rating *5        
    end    
    ) / SUM(rating)
    FROM #tableRatings

RESULT:

3.733870