问题描述:

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 #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 @SUMXY BIGINT

DECLARE @SUMXX2 BIGINT

DECLARE @SUMX2Y BIGINT

DECLARE @SUMX2Y_ BIGINT

DECLARE @SUMX2X2 BIGINT

DECLARE @N INT

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

网友答案:

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