This question already has answers here:
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
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
Thanks @Andy Korneyev...sum(cast (amount as decimal (10,2))) fixed the problem!
