2

I have stupid math problem. In T-SQL why are these two sums different:

select (34.3+17.5 + 13.5)/31.0*28.0   --= 58.9806280

select 34.3/31.0*28.0 + 17.5/31.0*28.0 + 13.5/31.0*28.0  --= 58.9806000
gotqn
  • 42,737
  • 46
  • 157
  • 243
vldmrrdjcc
  • 2,082
  • 5
  • 22
  • 41

1 Answers1

2

Calculation are done with the operator priority rule, and regarding the type of your numbers.

The first one select (34.3+17.5 + 13.5)/31.0*28.0 is done in 3 steps:

  • select (34.3 + 17.5 + 13.5) -- = 65.3
  • select 65.3 / 31.0 -- = 2.106451
  • select 2.106451 * 28.0 -- = 58.9806280

The second one is done this way:

  • Priority to the division: select 34.3 / 31.0 -- = 1.106451 select 17.5 / 31.0 -- = 0.564516 select 13.5 / 31.0 -- = 0.435483
  • Then: select 1.106451 * 28.0 -- = 30.9806280 select 0.564516 * 28.0 -- = 15.8064480 select 0.435483 * 28.0 -- = 12.1935240
  • Finally:select 30.9806280 + 15.8064480 + 12.1935240 -- = 58.9806000

And if you go deeper, take a look at this one too:

select cast(34.3 as float)/31.0*28.0 + 17.5/31.0*28.0 + 13.5/31.0*28.0
-- => 58,9806451332903

My english is not that good, and somebody else has already pointed out what you're asking for, so if you want to know why numbers are rounded that way, there are 2 great links that can help you: https://stackoverflow.com/a/424052/4584335 and https://msdn.microsoft.com/en-us/library/ms190476(SQL.90).aspx

Community
  • 1
  • 1
Rubik
  • 1,431
  • 1
  • 18
  • 24
  • From the Department of Unexpected Results: `select 30/2/5; select 30/-2/5; select 30/-2./5; select 30/(-2)/5;`. See [here](http://w3facility.org/question/sql-server-strange-math-result/) for more information. A change in _documented_ operator precedence between [SQL Server 2000](https://msdn.microsoft.com/en-us/library/aa276846%28v=sql.80%29) and [2005](https://msdn.microsoft.com/en-us/library/ms190276%28v=sql.90%29.aspx) makes this reasonable, if rather unconventional. – HABO Mar 10 '15 at 14:12