SQL Server giving arithmetic overflow when calculating avg

Question

I have a table with an integer column with some fairly large numbers in it. Im trying to average some values in this and sometimes it works other times it gives this error

"Arithmetic overflow error converting expression to data type int."

I've broken it down and this sample produces the error

create table LargeNumbers (number int)
insert into LargeNumbers values (100000000) -- X 30
select avg(number) from LargeNumbers

Does anyone know how I can get this to calculate the average?

Answer 1

You'll need to cast number to something larger than an int, say a biginteger, because to calculate the average SQL is summing all the values as an int, and this is where you are overflowing.

Answer 2

Internally SQL Server is summing the values (to divide by the count later) and storing them in the columns data type - in this case an int - which isn't large enough to hold the sum - if you cast the value as a bigint first it will sum the values also storing those values in a bigint - which is probably large enough, then the average calculation can proceed.

select avg(cast(number as bigint)) from LargeNumbers