Arithmetic overflow error converting expression to data type int for basic statistics

Question

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.dbo.table;

Where column is of type int, and the database is azure sql standard.

and in return, i get an error: "Arithmetic overflow error converting expression to data type int"

Why is int getting a casting-to-int problem in the first place? and is there something I can add that will make this work on all numerical types?

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:

 SELECT
 MIN(CAST(column as BIGINT)) as min,
 MAX(CAST(column as BIGINT)) as max,
 AVG(CAST(column as BIGINT)) 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.

SELECT 
 MIN(column) as min,
 MAX(column) as max,
 AVG(Cast(Column AS BIGINT)) 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.