SQL Server AVG 函数的奇怪之处

发布于 2024-08-04 06:38:13 字数 1129 浏览 4 评论 0原文

我在 SQL Server AVG 计算中看到一些奇怪的行为。

手动计算时，您会得到 49.277588
但 SQL Server 报告平均值为 50.9914
如下图所示。

问题：有人可以解释其中的差异以及为什么会发生这种情况吗？

您可以使用以下查询在 AdventureWorks2008 数据库上尝试查询

select  C.ProductCategoryID, P.ProductSubcategoryID,
        AVG(P.ListPrice) as 'Average',
        MIN(P.ListPrice) as 'Miniumum',
        MAX(P.ListPrice) as 'Maximum'
from    Production.Product P
        join Production.ProductSubcategory S 
            on S.ProductSubcategoryID = P.ProductSubcategoryID
        join Production.ProductCategory C 
            on C.ProductCategoryID = S.ProductCategoryID
where   P.ListPrice <> 0
group by C.ProductCategoryID, P.ProductSubcategoryID
with rollup

alt text

[更新] 答案< /强>
这是Excel中加权平均计算的结果

原文

I am seeing some odd behavior in SQL Server AVG calcuation.

On manual calculation, you get 49.277588
but SQL Server is reporting that the average is 50.9914
as shown below.

Question: Can someone explain the difference and why this is happening?

You can try out the query on AdventureWorks2008 Database with following query

select  C.ProductCategoryID, P.ProductSubcategoryID,
        AVG(P.ListPrice) as 'Average',
        MIN(P.ListPrice) as 'Miniumum',
        MAX(P.ListPrice) as 'Maximum'
from    Production.Product P
        join Production.ProductSubcategory S 
            on S.ProductSubcategoryID = P.ProductSubcategoryID
        join Production.ProductCategory C 
            on C.ProductCategoryID = S.ProductCategoryID
where   P.ListPrice <> 0
group by C.ProductCategoryID, P.ProductSubcategoryID
with rollup

alt text

[Update] Answer
Here is the result of Weighted Average calculation in Excel
alt text

分享到QQ

分享到微博