CODEWITHSUNDEEP
sqlsql-server
Sumedha Vangury
Sumedha Vangury

Reputation: 683

SUM(Column_Value) is giving a different value from calculator

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

enter image description here

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

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

Upvotes: 2

Views: 330

Answers (1)

Mazhar
Mazhar

Reputation: 3837

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

Upvotes: 3

Related Questions