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.

Code Maverick · Accepted Answer · 2014-02-12 23:44:47Z

57

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

edited Feb 12, 2014 at 23:44

Code Maverick

20.5k12 gold badges65 silver badges117 bronze badges

answered Nov 27, 2011 at 21:47

Michał Powaga

23.4k8 gold badges55 silver badges62 bronze badges

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

1 Comment

Matt Lyons-Wood Over a year ago

For what it's worth, the same seems to happen with AVG, even if it's just the sum of your values that's overflowing.

Code Maverick · Accepted Answer · 2014-02-12 23:38:53Z

1

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 ))

edited Feb 12, 2014 at 23:38

Code Maverick

20.5k12 gold badges65 silver badges117 bronze badges

answered Nov 27, 2011 at 21:52

JayC

7,1612 gold badges30 silver badges42 bronze badges

Comments

SuperBiasedMan · Accepted Answer · 2015-07-22 09:17:37Z

0

Accepted 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

edited Jul 22, 2015 at 9:17

SuperBiasedMan

10k11 gold badges52 silver badges76 bronze badges

answered Jul 22, 2015 at 8:43

user5142669

1

1 Comment

SuperBiasedMan Over a year ago

Does CAST( PokemonExp AS BIGINT ) not explicitly cast the result to BIGINT, which was the opposite of what OP wanted?

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related