Menu
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?
236
You need to increase the width of the variable to store this number e.g. making @sample NUMERIC (6,2) will solve this error.
"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.
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.
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.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
