SUM(Column_Value) is giving a different value from calculator

Question

Below are the column values in my sql table.When I add them up using a desktop calculator I get the sum as 0

But in sql when I take the sum of Item_Quantity, I am getting a strange value as below

The Data Type of this column is float. Can I please get some help on this? Thanks.

If you want to work with *precise decimal data*, then you've picked the wrong data type to represent it. What you're showing is a not unexpected result due to the fact that float cannot represent all *decimal* values precisely. — Damien_The_Unbeliever, May 01 '18 at 14:42
Possible duplicate of [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) — Nisarg Shah, May 01 '18 at 14:42
In case you're not familiar with the syntax, note that "-3.55E-15" means "-3.55 * 10 ^ -15", that is -0.00000000000000355 — IMSoP, May 01 '18 at 14:43
Basically you can change the column type to decimal if you need precise results. But keep in mind that decimal calculations are typically slower than float. I would suggest reading: https://stackoverflow.com/questions/1056323/difference-between-numeric-float-and-decimal-in-sql-server#7158770 — Nisarg Shah, May 01 '18 at 14:45
@Nisarg, couldn't you cast the sum to decimal to get the speed of the FP operation but the precision of decimal? E.g., `SELECT CAST(-0.00000000000000355 AS decimal)` returns 0. I guess that's not really *precision* - more like rounding. — David Faber, May 01 '18 at 14:50
@Nisarg I have changed the Data Type to decimal(18,0) but its still not giving me 0, its giving me -2 instead — Sumedha Vangury, May 01 '18 at 14:51
`decimal(18,0)` will not work as your data consists of decimal places, try `decimal(18,1)` — Mazhar, May 01 '18 at 14:53
float numbers are represent as a binary number internally. E.g. `0.2` is binary `0.00110011001100110011...` with and infinite number of repetitions of `0011`, which, of course, have to be truncated in the computer. Therefore the small error you are seeing. See: [Decimal to Binary converter](https://www.rapidtables.com/convert/number/decimal-to-binary.html) — Olivier Jacot-Descombes, May 01 '18 at 14:54
Personally, with that specific data, I'd use `decimal(3,1)` there's no need to have a scale higher than 3 considering the highest/lowest values are `12.8` and `-16.6` respectively. — Thom A, May 01 '18 at 14:54
@Mazhar thanks, but your solution did not work, I am still getting the value as -2.0 — Sumedha Vangury, May 01 '18 at 14:59
@sumedha You should use `decimal(19, 4)` - i.e. precision of 19 digits where 4 of those digits come after the decimal point. If you are working with smaller numbers you can use `decimal(10, 4)`. — Nisarg Shah, May 01 '18 at 15:02
If you are still unable to get the expected result, I would suggest creating a [mcve]. Perhaps you can use http://sqlfiddle.com/ to create a working example. — Nisarg Shah, May 01 '18 at 15:04
If you changed the column type to 18,0 you possibly removed the decimals now all your data is wrecked. — Jacob H, May 01 '18 at 15:04
Working example with `DECIMAL(18,1)`: http://sqlfiddle.com/#!18/603c9/2 — IMSoP, May 01 '18 at 15:05
@DavidFaber: it *is* rounding -- without further specification, `DECIMAL` stands for `DECIMAL(18, 0)`, that is, you're just rounding to a number with no digits after the decimal point. Naturally that'll get you to 0, but it's not the data type you want to use for storage. (And not specifying precision and scale is a bad idea, because the rules for determining precision and scale when different values are combined are rather arcane -- being explicit helps in staving off surprises.) — Jeroen Mostert, May 01 '18 at 15:07
@IMSoP, Thanks, I restored the database and checked it again, doing this works! thanks for the example — Sumedha Vangury, May 01 '18 at 15:17

score 3 · Accepted Answer · edited May 01 '18 at 15:44

Not sure why you're saying using DECIMAL(18,1) will not work

Here's your sample data

DECLARE @t TABLE ( Item_Quanity FLOAT)
INSERT INTO @t
(
    Item_Quanity
)
VALUES
(12.8) ,(-6.8) ,(1.4) ,(0.2) ,(3.4) ,(-2.8), (12) ,(-3.6) ,(-16.6)

Here's your query

SELECT SUM(Item_Quanity) FROM @t

which gives this result

(No column name)
-3.5527136788005E-15

Here's a modification of that query using CAST

SELECT SUM(CAST(Item_Quanity AS DECIMAL(18,1))) FROM @t

Which gives this result

(No column name)
0.0

Now changing the datatype from FLOAT to `DECIMAL(18,1)

DECLARE @t1 TABLE ( Item_Quanity DECIMAL(18,1))
INSERT INTO @t1
(
    Item_Quanity
)
VALUES
(12.8) ,(-6.8) ,(1.4) ,(0.2) ,(3.4) ,(-2.8), (12) ,(-3.6) ,(-16.6) 

SELECT SUM(Item_Quanity)
FROM @t1

Which returns

(No column name)
0.0

Thanks, this worked. I restored the database again and checked, its working now. — Sumedha Vangury, May 01 '18 at 15:22

SUM(Column_Value) is giving a different value from calculator

1 Answers1