SQL: Overflow error for type INT

问题:

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)

回答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