Arithmetic overflow error when summing an INT, how do I cast it as a BIGINT?

Brian picture Brian · Sep 4, 2009 · Viewed 57.1k times · Source

When I try to get the sum of a column from a table I get the error Arithmetic overflow error converting expression to data type int because the resulting number is to big for an INT. So I tried to CAST to a BIGINT using the following

SELECT CAST(SUM(columnname) AS BIGINT) FROM tablename

This gives me the same error. Any ideas what i'm doing wrong?

Answer

Robin Day picture Robin Day · Sep 4, 2009

Try converting it before summing. eg.

SELECT SUM(CONVERT(bigint, columnname)) FROM tablename

or

SELECT SUM(CAST(columnname AS BIGINT)) FROM tablename