SQL Server - avg decimal places

Asked By rodchar

03-Feb-10 12:07 PM

hey all,

given:
avg(cast(myvalue as numeric(12,2)))

in SQL Management Studio I am getting:
4.3333

I thought it would be 4.33 because I defined 2 decimal places.

Any ideas?

thanks,
rodchar

03-Feb-10 12:31 PM

This is because AVG of DECIMAL data type is treated as "DECIMAL(38, s) divided by DECIMAL(10, 0)":

http://msdn.microsoft.com/en-us/library/ms177677.aspx

You can cast the AVG result to desired precision:

SELECT AVG(CAST(x AS DECIMAL(12, 2))) AS avg_value
FROM (SELECT 1.231 UNION
SELECT 2.001) AS T(x);

/*

avg_value
---------------------------------------
1.615000

*/

SELECT CAST(AVG(x) AS DECIMAL(12, 2)) AS avg_value
FROM (SELECT 1.231 UNION
SELECT 2.001) AS T(x);

/*

avg_value
---------------------------------------
1.62

*/

--
Plamen Ratchev
http://www.SQLStudio.com

03-Feb-10 04:57 PM

thank you for the help,
rod.

03-Feb-10 09:20 PM

When you read the ANSI/ISO Standards, you saw the part of this being
implementation defined" of course, so you know this is a RTFM
question.

03-Feb-10 09:34 PM

Why would you expect that?  The average of a set of values can frequently be
finite and more precise than the input data.  Consider that the average of 2
& 3 is 2  1/2.  Since the size of the data set, in this case two, is an
exact value, and following propagation of error formulae, you can see that
there is a gain of significance in this case - this is common and expected.
The behaviour of SQL server is implementation specific, and may be hardware
dependent too (although nearly all platforms have now converged their FP &
BCD etc. behaviour).  If you are really interested in more detail, then
doing some research into numerical error theory will help tremendously.  If
not, then just cast the result to the desired precision and use it.

04-Feb-10 02:02 AM

--CLEKO-- you ignorant FW (work it out).

Just because you are too lazy to read the product manual (we all know you have
not RTFM'd because you would  understand about IDENTITY, you would  understand about
YYYY-MM-DD inconsistency, you would  understand a hell of a lot more and save us
having to constantly correct your error filled posts).

You are a coward, bullying people online yet in the flesh would not say boo
to a goose - a coward of the highest degree.

--ROGGIE--

04-Feb-10 08:33 AM

He does it to get your attention, not for himself so much, as your attention
to the subject.

Have to admit it works, bizarre, but it does work.