I am asking a followup question to a post earlier. (SQL: Overflow error for type INT). The issue I am having now is converting the results for @SUMX2Y and @SUMX2X2 to be FLOAT. I have tried multiple different methods and still having issues. Does anyone have an idea of what I'm doing wrong here? I did not include my conversion attempts, but they included CAST and CONVERT as far as the individual level of the equation.

``CREATE TABLE #Set1(X BIGINT,Y BIGINT)INSERT INTO #Set1VALUES (220, 630),(350, 940),(450, 1140)DECLARE @SUMX BIGINTDECLARE @SUMY BIGINTDECLARE @SUMX2 BIGINTDECLARE @SUMY2 BIGINTDECLARE @SUMX3 BIGINTDECLARE @SUMX4 BIGINTDECLARE @SUMXX BIGINTDECLARE @SUMXY_ BIGINTDECLARE @SUMXY BIGINTDECLARE @SUMXX2 BIGINTDECLARE @SUMX2Y BIGINTDECLARE @SUMX2Y_ BIGINTDECLARE @SUMX2X2 BIGINTDECLARE @N INTSET @N = (SELECT COUNT(*) FROM #Set1)SET @SUMY = (SELECT SUM(Y) FROM #Set1)SET @SUMX2 = (SELECT SUM(POWER(X,2)) FROM #Set1)SET @SUMX4 = (SELECT SUM(POWER(X,4)) FROM #Set1)SET @SUMX2Y_ = (SELECT SUM(POWER(X,2)*Y) FROM #Set1)SET @SUMX2Y = @SUMX2Y_ - (@SUMX2*@SUMY)/(@N)SET @SUMX2X2 = @SUMX4 - (POWER(@SUMX2,2)/@N)PRINT @SUMX2YPRINT @SUMX2X2``

I ended up just changing the BIGINT to FLOAT for now. I am unsure if down the road it causes issues. we'll see

Use NUMERIC(18,2) or whatever precision you want to keep. Using float this will keep decimal precision values until you explicitly pass decimal values

Below code you can use

``````CREATE TABLE #Set1
(
X NUMERIC(18,2),
Y NUMERIC(18,2)
)
INSERT INTO #Set1
VALUES (220, 630)
,(350, 940)
,(450, 1140)

DECLARE @SUMX NUMERIC(18,2)
DECLARE @SUMY NUMERIC(18,2)
DECLARE @SUMX2 NUMERIC(18,2)
DECLARE @SUMY2 NUMERIC(18,2)
DECLARE @SUMX3 NUMERIC(18,2)
DECLARE @SUMX4 NUMERIC(18,2)
DECLARE @SUMXX NUMERIC(18,2)
DECLARE @SUMXY_ NUMERIC(18,2)
DECLARE @SUMXY NUMERIC(18,2)
DECLARE @SUMXX2 NUMERIC(18,2)
DECLARE @SUMX2Y NUMERIC(18,2)
DECLARE @SUMX2Y_ NUMERIC(18,2)
DECLARE @SUMX2X2 NUMERIC(18,2)
DECLARE @N NUMERIC(18,2)

SET @N = (SELECT COUNT(*) FROM #Set1)
SET @SUMY = (SELECT SUM(Y) FROM #Set1)
SET @SUMX2 = (SELECT SUM(POWER(X,2)) FROM #Set1)
SET @SUMX4 = (SELECT SUM(POWER(X,4)) FROM #Set1)

SET @SUMX2Y_ = (SELECT SUM(POWER(X,2)*Y) FROM #Set1)

SET @SUMX2Y = @SUMX2Y_ - (@SUMX2*@SUMY)/(@N)
SET @SUMX2X2 = @SUMX4 - (POWER(@SUMX2,2)/@N)

PRINT @SUMX2Y
PRINT @SUMX2X2
``````

Use below script for Float & Numeric difference

``````DECLARE @SUMX NUMERIC(18,2) = 100

DECLARE @SUMX1 FLOAT = 100

SELECT @SUMX,@SUMX1
``````

Top