SQL Server giving arithmetic overflow when calculating avg

Sql ServerTypes

Sql Server Problem Overview


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?

Sql Server Solutions


Solution 1 - Sql Server

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

Solution 2 - Sql Server

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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionuser129211View Question on Stackoverflow
Solution 1 - Sql ServerTetraneutronView Answer on Stackoverflow
Solution 2 - Sql ServerStephen NuttView Answer on Stackoverflow