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

Question

My question is similar to the one in How to prevent arithmetic overflow error when using SUM on INT column? but limit the int value.

I want to insert the max limit of the int(2147483647) if the sum(column_name) exceeds the int limit, how to do it? Note: TotalExp datatype is INT

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

Try replacing your current `SUM(PokemonExp) AS TotalExp` with `CASE WHEN SUM(PokemonExp) >= 2147483647 THEN 2147483647 ELSE SUM(PokemonExp) END AS TotalExp`. Note: Pseudo-code. — SchmitzIT, Apr 12 '18 at 11:39
@SchmitzIT when you sum integer columns that would result in a value above 2147483647, you get an overflow error. Which means your suggestion doesn't work. You can try this example: `SELECT CASE WHEN sum(a)> 1 THEN 1 ELSE 0 END FROM (values(1),(2147483647)) x(a)` — t-clausen.dk, Apr 12 '18 at 12:18

score 0 · Accepted Answer · answered Apr 12 '18 at 11:56

Convert PokemonExp to bigint

INSERT INTO NEWTABLE
SELECT
  UserId,
  CASE WHEN 
    SUM( CAST(PokemonExp as BIGINT)) > 2147483647
         THEN 2147483647 
         ELSE SUM( CAST(PokemonExp as BIGINT))
         END AS TotalExp,
  MAX( PokemonLevel ) AS MaxPokeLevel
FROM      mytable
GROUP BY  UserId
ORDER BY  TotalExp DESC

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

1 Answers1