How to CAST to a BIGINT in a query

Question

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

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

but I get the same error. Any ideas what i'm doing wrong?

Answer 1

Try converting it before summing. eg.

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

or

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