0

Why does this query returns different when I do it in excel or calculator?

select 0.1/12 * 600000

It returns 4999.800000

How do I make it to 5000?

Dekso
  • 541
  • 4
  • 23
  • 1
    Those aren't decimals. They are *floats*. `0.1/12` returns `0.008333`. – Panagiotis Kanavos Aug 03 '18 at 08:56
  • Are you asking because you encountered an actual problem? If the values come from `float` fields or parameters, the solution is to *not* use floats, switch to `numeric/decimal` with a specific precision. – Panagiotis Kanavos Aug 03 '18 at 08:59
  • SQL Server tries to preserve the number of decimal digits used in numeric literals, so `0.1000000000/12 ` will return `0.0083333333333`. That's still an [irrational number](https://en.wikipedia.org/wiki/Irrational_number) though, still imprecise. You can change the calculation order to avoid irrational intermediate results, eg `600000/12 * 0.1` will return `5000.0` – Panagiotis Kanavos Aug 03 '18 at 09:03
  • @PanagiotisKanavos 0.1/12 returns a numeric, not a float, a float would have returned the correct value in this case. SELECT cast(0.1 as float)/12*600000, SQL_VARIANT_PROPERTY(0.1/12, 'BaseType') – t-clausen.dk Aug 03 '18 at 09:08
  • @t-clausen.dk it can't, it's an irrational number. – Panagiotis Kanavos Aug 03 '18 at 09:09
  • @t-clausen.dk `declare @i float=0.1, @j float=12; select @i/@j` returns `0.00833333333333333`. If `5000` is returned, it's the result of rounding – Panagiotis Kanavos Aug 03 '18 at 09:10
  • @PanagiotisKanavos Yes it rounds, but that doesn't make it a float – t-clausen.dk Aug 03 '18 at 09:17
  • @PanagiotisKanavos, `EXEC sp_describe_first_result_set N'select 0.1/12';` returns type `numeric(6,6)`. The value of the result `0.008333'. Multiplying that value by the integer 600000 yields data type `numeric(13,6)` with a value of `4999.800000`. – Dan Guzman Aug 03 '18 at 09:33
  • @t-clausen.dk as someone complained when I said `It's a decimal`, both are floating point numbers. The rounding rules are unexpected though or rather, there's a scaling issue. decimals are supposed to be more precise, they should return *more* 8s. And yet the *greater* the precision for `12`, the worse the actual result is! `0.1/12` will return `0.008333` if 12 is a `numeric(38,17)` and a final result `4999.800000`. If it's a `numeric(38,0)`, the division produces `0.00833333333333333` and a final result of `5000.000000` ! `(38,6) gives a final result of `4999.999998` – Panagiotis Kanavos Aug 03 '18 at 09:33
  • @DanGuzman I don't care, I'm having too much fun watching how scaling errors play havoc with the values. – Panagiotis Kanavos Aug 03 '18 at 09:34
  • @DanGuzman even after explicitly using integers, the precision used for `0.1` matters. If `0.1` is a numeric with scale>=12, the end result is scaled to 5000. Less than that, you an irrational number with a scale of only 6 – Panagiotis Kanavos Aug 03 '18 at 09:38
  • @PanagiotisKanavos btw, 0.0083333333333... is not an irrational number - it is a repeating decimal – t-clausen.dk Aug 03 '18 at 09:44

0 Answers0