How to prevent arithmetic overflow error when using SUM on INT column?

Question

I am using SQL Server 2008 R2 and I have an INT column where the data inserted never surpasses the max INT, but I have a query which uses the SUM function which when executed surpasses the max INT limit and throws the error mentioned in the title.

I want to be able to execute this query without changing the column type from INT to BIGINT.

Here is my query:

SELECT    UserId,           SUM( PokemonExp )     AS TotalExp,           MAX( PokemonLevel )   AS MaxPokeLevel  FROM      mytable  GROUP BY  UserId ORDER BY  TotalExp DESC 

Note: The PokemonExp column is of type INT.

Answer 1

Type of expression in SUM determines return type.

Try the following:

SELECT    UserId,           SUM( CAST( PokemonExp AS BIGINT ))  AS TotalExp,           MAX( PokemonLevel )                 AS MaxPokeLevel  FROM      mytable  GROUP BY  UserId ORDER BY  TotalExp DESC 

Answer 2

You don't have to change the column type to BIGINT to get a proper sum.

Just CAST or CONVERT PokemonExp to BIGINT before you perform the SUM like follows:

SUM( CAST( PokemonExp AS BIGINT ))