Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 236
user129211 Avatar asked Jul 29 '09 02:07

user129211


People also ask

How do you fix arithmetic overflow in SQL?

You need to increase the width of the variable to store this number e.g. making @sample NUMERIC (6,2) will solve this error.

What is arithmetic overflow error in SQL Server?

"Arithmetic overflow error converting IDENTITY to data type int" error means the value of IDENTITY is overflowing range of data type of that particular column.

How do you calculate average in SQL Server?

AVG () computes the average of a set of values by dividing the sum of those values by the count of nonnull values. If the sum exceeds the maximum value for the data type of the return value, AVG() will return an error.

Why do we call AVG () as aggregate function in SQL?

SQL Server AVG() function is an aggregate function that returns the average value of a group. In this syntax: ALL instructs the AVG() function to take all values for calculation. ALL is used by default.


1 Answers

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 
like image 156
Tetraneutron Avatar answered Oct 22 '22 11:10

Tetraneutron