SUM 函数添加不正确 [英] SUM Function Does Not Add Correctly

问题描述

我有一个表格，我可以从中计算特定列中的值的总和.表是:

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

查询是:

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

这应该给我 72200 的总和，但它返回 72199.999999999985.但是，它只对355组这样做，对于其他组，计算是正确的

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

推荐答案

我不确定您的数据类型.但请参阅以下返回值示例.在 MSSQL 2012 中，您的情况似乎对应于数据类型 REAL.MONEY、FLOAT、DECIMAL、DOUBLE PRECISION 返回 72200.00 REAL 返回 72199.9998321533.见下文:

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

您可能想要更改列数据类型(如果它不是所需的类型，可能是从 SQL Studio 等的下拉菜单中错误设置)或将值转换为上述数据类型之一，它将返回想要的 72200.00

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

这篇关于SUM 函数添加不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！