SQL Server theory question 2

Question

Hello,
Let me please clarify one more question. While practicing GROUP BY clause I decided to manually check the values SQL Server outputs when counting average values and was suprised to see a mismatch in one of the examples:

I manually counted the average for the BusinessEntityId 1492 -

(39.7775 + 38.295 + 35.26 + 44.26 + 43.95 +43.295) / 6 and got 40.8058... - not the 40.9068 as in the first output.
Should these values match or my counting is incorrect?

Thank you in advance,
Michael

Answer 1

If you are going to do this, you need to include the COUNT() and sum the COUNT() from the 2nd query to get the avg, not just add up the row count.

Also Avg(Avg()) is not the same as Avg().

https://support.zendesk.com/hc/en-us/articles/115000618148-Calculating-the-total-average-of-individually-averaged-values#:~:text=In%20most%20of%20the%20cases,the%20same%20number%20of%20values.

Answer 2

Hello Michael,

You calculate in the second example the average of the 6 average values; do you really expect the same result as the average over all?
That has nothing to do with SQL Server, that's simple math.

Answer 3

HI @Mikhail Firsov ,

There is a simple example which may be enough to explain what goes wrong.

For example: the average of {1,1,1} is 1, (N=3) and the average of {2} is 2, (N=1).The average of the averages is (1+2)/2=1.5. But the average of all numbers is 5/4 =1.25.

In your example, only when all of LastReceiptDate are completely the same with each other in every BusinessEntityID, then you could get the same average value, otherwise you will get different average value.

You could also refer this article for more details about why is an average of an average usually incorrect.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Thank you all for your replies!

I asked that question after checking the average for the manually created table and getting the same values - but that was exactly the case described here:

"They can be equal only if all of the averaged values are computed over the numeric sets with the same number of values."

Thank you all once again!

Regards,
Michael