0

I have a table with the following:

Name  Number Pct
A     12.1     Null
A     24.5     Null
A      5.6     Null
B     10.4     Null
B     11.1     Null
etc.

I am trying to populate the Pct column and for that I use:

UPDATE Table SET
Pct= Number/ (SELECT SUM(b.Number) FROM Table b WHERE name = b.name GROUP by b.name);

On my results the sum of all A Pcts is 1. But when I sum all Bs I get something like 1.0000015. If I copy paste the data to Excel, sum all Numbers by name and divide I get 1 for everything.

Is it possible that SQLite is loosing precision somewhere?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Yona
  • 571
  • 7
  • 23
  • That is an inherent problem of working with floating-point numbers, see http://floating-point-gui.de/ // Since SQLite doesn’t seem to have a DECIMAL data type like f.e. MySQL has, one workaround could be that you store integers instead, so `121` instead of `12.1`, then work with those values, only transform them back when the end result is displayed to the user. – CBroe Dec 04 '15 at 14:14
  • Thanks CBroe, I am still confused on how to implement your solution. I tried to do a CAST(number * 10000 as INT) but then any division yields 0 because all decimals are ignored. I am kind of stuck here! – Yona Dec 04 '15 at 14:39

0 Answers0