SQL: Overflow error for type INT

Question

I have the below SQL code and I am trying to work on creating a formula for second order polynomial regression. While I am doing this, @SUMX4 is receiving an error as follows:

Msg 232, Level 16, State 3, Line 18 Arithmetic overflow error for type int, value = 2342560000.000000.

I converted all variables as BIGINT and still getting this error. Any ideas?

create table Set1
(X INT,
Y INT)
INSERT INTO Set1 VALUES
 (220, 630)
,(350, 940)
,(450, 1140);


DECLARE @SUMX BIGINT
DECLARE @SUMY BIGINT
DECLARE @SUMX2 BIGINT
DECLARE @SUMY2 BIGINT
DECLARE @SUMX3 BIGINT
DECLARE @SUMX4 BIGINT
DECLARE @SUMXX BIGINT
DECLARE @SUMXY BIGINT
DECLARE @SUMXX2 BIGINT
DECLARE @SUMX2Y BIGINT
DECLARE @SUMX2X2 BIGINT

SET @SUMX = (SELECT SUM(X) FROM SET1)
SET @SUMY = (SELECT SUM(Y) FROM SET1)
SET @SUMX2 = (SELECT SUM(POWER(X,2)) FROM SET1)
SET @SUMY2 = (SELECT SUM(POWER(Y,2)) FROM SET1)
SET @SUMX3 = (SELECT SUM(POWER(X,3)) FROM SET1)
SET @SUMX4 = (SELECT SUM(POWER(X,4)) FROM SET1)


PRINT CONCAT('SUMX: ', @SUMX)
PRINT CONCAT('SUMY: ', @SUMY)
PRINT CONCAT('SUMX2: ', @SUMX2)
PRINT CONCAT('SUMY2: ', @SUMY2)
PRINT CONCAT('SUMX3: ', @SUMX3)
PRINT CONCAT('SUMX4: ', @SUMX4)
PRINT CONCAT('SUMXX: ', @SUMXX)
PRINT CONCAT('SUMXY: ', @SUMXY)
PRINT CONCAT('SUMXX2: ', @SUMXX2)
PRINT CONCAT('SUMX2Y: ', @SUMX2Y)
PRINT CONCAT('SUMX2X2: ', @SUMX2X2)

Gareth Lyons · Accepted Answer · 2017-01-26 18:34:56Z

1

SUM returns the same datatype as the expression you're summing. Try casting to bigint like this:

SET @SUMX = (SELECT SUM(CAST(X AS BIGINT)) FROM SET1)
...
SET @SUMX2 = (SELECT SUM(POWER(CAST(X AS BIGINT),2)) FROM SET1)
...

for all sums

answered Jan 26, 2017 at 18:34

Gareth Lyons

1,97214 silver badges14 bronze badges

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

4 Comments

SeanDon Over a year ago

Even if they are already BIGINT? shouldn't sum know that I'm adding BIGINT and create a BIGINT?

Gareth Lyons Over a year ago

It's the fields, not the variables. Your create table statement defines X and Y as INT.

SeanDon Over a year ago

You're right! not sure why I didn't see that! I was so focused on everything after it. Thanks!

Gareth Lyons Over a year ago

No worries mate

Collectives™ on Stack Overflow

SQL: Overflow error for type INT

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related