AVG Function Overflow

Convert the column to BIGINT so you have more range: SELECT AVG(CONVERT(BIGINT, YourColumn)) FROM YourTable -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <Dennis Jackson@.discussions.microsoft.com> wrote in message news:43ea50a8-ae33-4816-bb70-8935293b923c@.discussions.microsoft.com...encountered on SQL 2005 CTP Sept.When I am using the AVG function of 2 years worth of stock volume data(503 data points), I get a INT buffer overflow. However, the STDEV function continues to work, which I *think* requires you to calculate average first...Any Suggestions on how to work around this?encountered on SQL 2005 CTP Sept.

When I am using the AVG function of 2 years worth of stock volume data(503 data points), I get a INT buffer overflow.

However, the STDEV function continues to work, which I *think* requires you to calculate average first...

Any Suggestions on how to work around this?|||The reason it doesn't work is AVG is computed as SUM/COUNT and not implemented natively. Since SUM returns for any expression of integer category except bigint, it will error out depending on the values. Ideally, we should implement AVG natively to handle the domain of values. For now, workaround is to cast the expression to higher precision data type.|||I already have the variable defined as a 4byte unsigned INT.
I may try to do AVG(column/1000000).. i will see if that will work.|||I assume then that STDEV is implemented as a native function then.
that was the part that wosnt making sense.. you need average to calculate STDEV, and that worked. but the logical intermediate item it couldnt calculate.

Also another wierd thing I ran across. when I tried to create a column that was stdevP(column) when the sql management studio parses it, it keeps converting it to COUNTBig(column) weird.

Do you know if that is a change they are planning to make?