计算加权平均单查询

Question

示例数据：

table A 
part rating numReviews 
A308 100  7 
A308 98  89 

我想获得的平均得分为上述数据。

什么它需要是rating * numReviews每条线由总numReviews

这分总和我尝试，但它给不正确的结果（49.07，应该是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 

这可以在一个查询中完成吗？我使用SQL Server

Answer 1

只需返回到加权平均的定义，所以使用sum() S和分工：

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

可以将此转换为十进制，如果你喜欢：

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