示例数据:
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
回到加权平均的定义,所以使用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;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)