The same mathematical logic, different results?

Question

How do I get different results from the following two queries? a, b, c is a float, and I would suggest that they return the same results, but they turn off a bit.

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

Answer 1

Are any of your values ​​NULL?

Let's say you have data such as:

a        b        c
NULL     1        2
1        2        3

Your first query will return 3.

The second query will return 2.

Answer 2

When you do arithmetic on a lot of floating point numbers, the order matters. Canonical example:

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

If it is rated as:

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

38. "38" , .

:

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

0.

decimal .

Answer 3

Welcome to the world of floats. And this is not about large quantities or small quantities.
It is about limiting the accuracy of this method of representing numbers.

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