CODEWITHSUNDEEP
sql-servertypes
user129211
user129211

Reputation: 585

SQL Server giving arithmetic overflow when calculating avg

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?

Upvotes: 55

Views: 24233

Answers (2)

Tetraneutron
Tetraneutron

Reputation: 33831

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

Upvotes: 101

Stephen Nutt
Stephen Nutt

Reputation: 3306

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.

Upvotes: 7

Related Questions