CODEWITHSUNDEEP
sqlsql-servert-sqlexcelweighted-average
dance2die
dance2die

Reputation: 36915

SQL Server AVG function oddity

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

Upvotes: 0

Views: 2076

Answers (1)

David
David

Reputation: 73564

It looks like you're doing an average of an average in Excel, which is bad math.

http://wiki.answers.com/Q/Is_an_average_of_averages_accurate

Upvotes: 4

Related Questions