Different results returned between SUM()/total rows and AVG() in sql server

Question

I have a table #time_per_bike with 6549 rows (no duplicates present. Even the bikeid column has unique IDs). Please see the sample -

Can anybody tell me why am I getting different results between the two queries (since I believe they do same operation. Please correct me if I am wrong)?

select SUM(AVG_WAIT_TIME)/6549 from #time_per_bike
-- Returns 69499

select AVG(AVG_WAIT_TIME) from #time_per_bike
-- Returns 69520

Answer 1

"Except for the COUNT(*) function, all aggregate functions perform a Null-elimination step, so that Null values are not included in the final result of the calculation." Ref: http://en.wikipedia.org/wiki/Null_(SQL)

So you have some NULLs present in your data.

You have two choices depending how you want to treat nulls in your data. You can either treat as a zero value, or eliminate the rows entirely so they won't contribute to COUNT(*) (as AVG() does).

From referenced wiki page:

For example, in the following table, AVG(i) (the average of the values of i) will give a different result from that of AVG(j):

Table
i         j
150     150
200     200
250     250
NULL      0

Here AVG(i) is 200 (the average of 150, 200, and 250), while AVG(j) is 150 (the average of 150, 200, 250, and 0).

A well-known side effect of this is that in SQL AVG(z) is not equivalent with SUM(z)/COUNT(*)