2011-11-30

T-SQL AVG - "Arithmetic overflow error converting expression to data type int."

I ran into an a bit of interesting conundrum the other day in a SQL job I was writing. It passed review, and then failed in QA, and it turned out to be a bug/unexpected behavior in the way SQL processes datasets with the AVG command.
Consider this code example:
DECLARE @t TABLE (COL_1 INT)
INSERT INTO @t SELECT 1
INSERT INTO @t SELECT 2147483647 -- MAXIMUM VALUE OF INT
SELECT AVG(COL_1) FROM @t
You get an ugly error that looks like this:
Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type int.
For some reason the SQL team decided to use the native type of the column for computations, and so when the values are SUMmed, it obviously exceeds the value of INT. SQL Then throws out that ugly error.
Try this instead:
DECLARE @t TABLE (COL_1 INT)
INSERT INTO @t SELECT 1
INSERT INTO @t SELECT 2147483647 -- MAXIMUM VALUE OF INT
SELECT AVG(CAST(COL_1 AS BIGINT)) FROM @t
Success!
Zoidberg Away!

1 comment:

Sebastian said...

Thanks, I had the exact same error and this was the solution I was looking for!