Arithmetic Overflow Error Converting Expression To Data Type Int For Basic Statistics

I am trying to do a basic query that calculates average, min, max, and count. SELECT MIN(column) as min, MAX(column) as max, AVG(column) as avg, count(*) as count FROM database

Solution 1:

Try to use count_big like this:

SELECT
 MIN(column) as min,
 MAX(column) as max,
 AVG(column) as avg,
 count_big(*) as count
FROM database.dbo.table;

Also you try to CAST your column as BIGINT like this:

SELECTMIN(CAST(columnasBIGINT)) as min,
 MAX(CAST(columnasBIGINT)) as max,
 AVG(CAST(columnasBIGINT)) as avg,
 count_big(*) as count
FROM database.dbo.table;

Solution 2:

The issue is either with the average aggregation or the count(*).

If the sum of the values in column is greater than 2,147,483,647 you will need to cast the column as a bigint before averaging because SQL first sums all of the values in column then divides by the count.

If the count of the rows is more than 2,147,483,647, then you need to use count_big.

The code below includes both fixes so it should work.

SELECTMIN(column) as min,
 MAX(column) as max,
 AVG(Cast(ColumnASBIGINT)) as avg,
 count_big(*) as count
 FROM database.dbo.table;

You don't need to cast min or max to bigint because these values exist in the table already without overflowing the int data type.