Calculate weighted average in single query

Question

Example data:

table A
part    rating   numReviews
A308    100      7
A308    98       89

I'm trying to get the average rating for the above data.

What it needs to be is the sum of rating*numReviews for each line divided by the total numReviews

This is what I'm trying but it's giving incorrect result (49.07, should be 98.15):

select part, 
cast((AVG(rating*numReviews)/sum(numReviews)) as decimal(8,2)) as rating_average
from A group by part order by part

Can this be done in a single query? I'm using SQL Server

Answer 1

Just go back to the definition of weighted average, so use sum()s and division:

select part, sum(rating * numreviews) / sum(numreviews) as rating_average
from a
group by part
order by part;

You can convert this to a decimal if you like:

select part,
       cast(sum(rating * numreviews) / sum(numreviews) as decimal(8, 2)) as rating_average
from a
group by part
order by part;