CODEWITHSUNDEEP
sqlsql-servert-sql
precose
precose

Reputation: 141

Same math logic, different results?

How am I getting different results from the following two queries? a,b,c are float and I would assume these return the same results but they are slightly off.

SELECT (a-b)+(c) 
FROM
    (
            select  sum([Actual Freight Per Line Amt]) a, 
                    sum([fedex charge per line amt]) b, 
                    sum([inbound freight cost]) c 
             from stg.invoices where year([gl date]) = '2016'
    ) foo

results in: -5822899.31314175

&

SELECT SUM((a-b)+(c))
FROM
    (
            select  [Actual Freight Per Line Amt] a, 
                    [fedex charge per line amt] b, 
                    [inbound freight cost] c 
             from stg.invoices where year([gl date]) = '2016'
    ) foo

results in: -5796251.59304654

Upvotes: 4

Views: 123

Answers (3)

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

Welcome to the world of floats.
And it is not about big numbers or small numbers.
It is about the accuracy limitation of this numbers' representing method. 

declare @t table (f float);
insert into @t(f) values (0.1),(0.2),(-0.2),(-0.1);
select sum(f) from @t;

2.77555756156289E-17

declare @t table (f float);
insert into @t(f) values (0.1),(0.2),(-0.3);
select sum(f) from @t;

5.55111512312578E-17

Upvotes: 1

Derrick Moeller
Derrick Moeller

Reputation: 4950

Are any of your values NULL?

Say, you have data such as:

a        b        c
NULL     1        2
1        2        3

Your first query will provide a result of 3.

Your second query will provide a result of 2.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269923

When you are doing arithmetic on a lot of floating point numbers, the ordering matters. A canonical example is:

1,000,000,000,000,000,000,000,000,000 + -1,000,000,000,000,000,000,000,000,000 + 38

If this is evaluated as:

(1,000,000,000,000,000,000,000,000,000 + -1,000,000,000,000,000,000,000,000,000) + 38

You'll get 38. The "38" is so much smaller than the other number that a floating point representation cannot represent the value.

If it is evaluated at:

1,000,000,000,000,000,000,000,000,000 + (-1,000,000,000,000,000,000,000,000,000 + 38)

You'll get 0.

I recommend that you use decimals for the calculation.

Upvotes: 5

Related Questions