SUM Function Does Not Add Correctly [duplicate]

Question

This question already has answers here:

Difference between numeric, float and decimal in SQL Server (8 answers)

Closed 4 years ago.

I have a table from which I calculate the sum of values in a particular column. The table is:

PayTable

Amount       GRPS        
65,025.00    355
-2,500.00    355
7,014.40     355
725.62       355
241.67       355
1,209.57     355
241.87       355
241.87       355

The query is:

SELECT   GRPS,SUM(Amount) AS AMT into AmtTab FROM PayTable GROUP BY  GRPS

This should give me a sum of 72200, however it returns 72199.999999999985 instead. However, it only does this for the group 355, for other groups, the calculation is correct

Answer 1

I am not sure about your datatype. But see the following examples of returned values. In MSSQL 2012 your situation seems to correspond to the datatype REAL. While MONEY, FLOAT, DECIMAL, DOUBLE PRECISION return 72200.00 REAL returns 72199.9998321533. See below:

CREATE TABLE #tempso (Amount money, GRPS int);

INSERT INTO #tempso(Amount, GRPS) VALUES
('65025.00',355),
('-2500.00',355),
('7014.40',355),
('725.62',355),
('241.67',355),
('1209.57',355),
('241.87',355),
('241.87',355)

SELECT GRPS,SUM(Amount) AS AMT FROM #tempso GROUP BY  GRPS

ALTER TABLE #tempso ALTER COLUMN Amount float
SELECT GRPS,SUM(Amount) AS AMT FROM #tempso GROUP BY  GRPS

ALTER TABLE #tempso ALTER COLUMN Amount decimal(10,2)
SELECT GRPS,SUM(Amount) AS AMT FROM #tempso GROUP BY  GRPS

ALTER TABLE #tempso ALTER COLUMN Amount DOUBLE PRECISION
SELECT GRPS,SUM(Amount) AS AMT FROM #tempso GROUP BY  GRPS

        --------------- 
RETURNS:|355 72200.00 |
        ---------------

ALTER TABLE #tempso ALTER COLUMN Amount REAL
SELECT GRPS,SUM(Amount) AS AMT FROM #tempso GROUP BY  GRPS

        ----------------------  
RETURNS:|355 72199.9998321533 |
        ----------------------

DROP TABLE #tempso

You may want to change the column datatype (if it is not a desired type, perhaps it was set by mistake from the dropdown menu of SQL Studio, etc.) or CAST the value as one of the above datatypes which will return desired 72200.00

Answer 2

Thanks @Andy Korneyev...sum(cast (amount as decimal (10,2))) fixed the problem!