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?

Rahul Tripathi · Accepted Answer · 2015-04-08 18:05:00Z

3

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;

edited Apr 8, 2015 at 18:05

answered Apr 8, 2015 at 17:59

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ckarst · Accepted Answer · 2015-04-08 20:11:15Z

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.

edited Apr 8, 2015 at 20:11

answered Apr 8, 2015 at 18:18

ckarst

7615 silver badges11 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related