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
– SchmitzIT, Commented Apr 12, 2018 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
– t-clausen.dk, Commented Apr 12, 2018 at 12:18

t-clausen.dk · Accepted Answer · 2018-04-12 11:56:59Z

0

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

answered Apr 12, 2018 at 11:56

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related