N1ql SUM of float made different result

SQL++
,
1

we have following data and when we call SUM over those values gives different result

[

{
“amount”: 100 },
{
“amount”: 7000
},
{
“amount”: 400
},
{
“amount”: 9880
},
{
“amount”: 700
},
{
“amount”: 0.01
},
{
“amount”: 0
},
{
“amount”: 100
},
{
“amount”: 9000
},
{
“amount”: 0
},
{
“amount”: 18
},
{
“amount”: 200
},
{
“amount”: 200
},
{
“amount”: 0.1
},
{
“amount”: 0
},
{
“amount”: 0.009
},
{
“amount”: 99
},
{
“amount”: 32755
},
{
“amount”: 14620
},
{
“amount”: 15200
},
{
“amount”: 1023
},
{
“amount”: 200
},
{
“amount”: 3211
},
{
“amount”: 1220
},
{
“amount”: 90000
}
]

the query is

select sum(case move_type when 'outcome' then a.amount * -1 else a.amount end ) s from `safety-checklist` as a where model = 'stockmove'

the results are:
12110.118999999991
12110.119000000006
12110.118999999998

how we should sum up our stored values correctly?

2

I think what’s going on here is that the result differs slightly depending on what order the additions are done in. It’s well known that innocuous base-10 numbers like 0.1 have complicated floating-point representations, and that causes weird effects when they are used for math.

Could you try storing the numbers in a form where they are integers? That should make the issue go away.

3

Try using ROUND to remove floating point variations.

select ROUND(sum(case move_type when 'outcome' then a.amount * -1 else a.amount end ),10) s fromsafety-checklistas a where model = 'stockmove';

ROUND(expression [, digits ])

Rounds the value to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.

4

Thank you for solution and example
ROUND made trick