SQL Server sum zero but result not zero-CodePudding

I try to roll amount or sum of amount but result -3,5527136788005E-15. I calculate manually amount, result equal zero. What is problem? Any idea of this situation?

SELECT
    XX.*,
    SUM(QTY) OVER(order by DATE_, FICHENO, STFICHELNNO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as TOTALQTY
FROM (
SELECT
    STFICHE.DATE_ DATE_,
    STFICHE.FICHENO FICHENO,
    STLINE.STFICHELNNO STFICHELNNO,
    ITEMS.CODE CODE,
    CASE WHEN STLINE.IOCODE IN(1,2) THEN 'Giriş' when STLINE.IOCODE IN(3,4) THEN 'Çıkış' else '' end DIRECTION,
    CAST((CASE WHEN STLINE.IOCODE IN(1,2) THEN 1 ELSE -1 end) * STLINE.AMOUNT*(CASE WHEN ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END)/(CASE WHEN ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END)  AS float) QTY,
    (SELECT NR FROM L_CAPIWHOUSE CAPIWHOUSE WHERE CAPIWHOUSE.NR = STLINE.SOURCEINDEX AND CAPIWHOUSE.FIRMNR = 220 ) CAPIWHOUSE,
    STLINE.SPECODE AS HAREKET_OZEL_KODU
FROM LG_220_01_STFICHE STFICHE WITH (NOLOCK)
    LEFT OUTER JOIN LG_220_01_STLINE STLINE WITH (NOLOCK) ON STFICHE.LOGICALREF=STLINE.STFICHEREF
    LEFT OUTER JOIN LG_220_ITEMS ITEMS      WITH (NOLOCK) ON STLINE.STOCKREF =ITEMS.LOGICALREF
    LEFT OUTER JOIN LG_220_UNITSETL BIRIM   WITH (NOLOCK) ON BIRIM.UNITSETREF= ITEMS.UNITSETREF AND BIRIM.MAINUNIT=1
    LEFT OUTER JOIN  LG_220_SRVCARD SRV ON SRV.LOGICALREF=STLINE.STOCKREF
    LEFT OUTER JOIN LG_220_ITMCLSAS AS CLS ON CLS.CHILDREF = ITEMS.LOGICALREF AND CLS.UPLEVEL = 0   AND CLS.PARENTREF>0
WHERE
    STFICHE.CANCELLED=0 AND STLINE.CANCELLED=0
    AND STLINE.LINETYPE=0
    AND STLINE.IOCODE IN (1,2,3,4)
    AND STLINE.LPRODSTAT=0
    AND STFICHE.DATE_>= '01.05.2022'
) AS XX WHERE CODE='91.KI10.0045'
    AND HAREKET_OZEL_KODU=''
    AND CAPIWHOUSE='4'
ORDER BY DATE_

If I try only SUM

SELECT
    SUM(QTY)
FROM (
SELECT
    ............... same query as above
) AS XX WHERE CODE='91.KI10.0045'
    AND HAREKET_OZEL_KODU=''
    AND CAPIWHOUSE='4'

result = -3,5527136788005E-15

CodePudding user response：

Don't use approximate numeric types like float and real if you require accuracy in your calculations. From the float and real (Transact-SQL) documentation:

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

Using your own data from the question as an example...

create table dbo.Example (
  AMOUNT float
);

insert dbo.Example (AMOUNT) values
  (0.45),
  (24.955),
  (-2),
  (-1),
  (-2),
  (-1),
  (-19.405);

select AMOUNT from dbo.Example;

|  AMOUNT |
|--------:|
|    0.45 |
|  24.955 |
|      -2 |
|      -1 |
|      -2 |
|      -1 |
| -19.405 |

select sum(AMOUNT) as [TOTAL] from dbo.Example;

|                TOTAL |
|---------------------:|
| -3.5527136788005E-15 |