SQLite Forum

It is not hard at all.  And the problem you posit does not exist.  Consider the following where xi is in "pennies" and xf is in floating "dollars":

```
sqlite> create table x(xi, xf);
sqlite> insert into x (xi) select randomv(200000) from wholenumber where value between 1 and 1000000;
sqlite> update x set xf = xi/100.0;
sqlite> select * from x limit 10;
┌────────┬─────────┐
│   xi   │   xf    │
├────────┼─────────┤
│ 107368 │ 1073.68 │
│ 125617 │ 1256.17 │
│ 126619 │ 1266.19 │
│ 28429  │ 284.29  │
│ 175912 │ 1759.12 │
│ 185600 │ 1856.0  │
│ 67209  │ 672.09  │
│ 96405  │ 964.05  │
│ 94741  │ 947.41  │
│ 33114  │ 331.14  │
└────────┴─────────┘
sqlite> select sum(xi)/100.0, sum(xf) from x;
┌───────────────┬─────────────┐
│ sum(xi)/100.0 │   sum(xf)   │
├───────────────┼─────────────┤
│ 999476637.6   │ 999476637.6 │
└───────────────┴─────────────┘
sqlite> select avg(cast(xi as real))/100.00, avg(xf) from x;
┌──────────────────────────────┬─────────────┐
│ avg(cast(xi as real))/100.00 │   avg(xf)   │
├──────────────────────────────┼─────────────┤
│ 999.4766376                  │ 999.4766376 │
└──────────────────────────────┴─────────────┘
sqlite>
```

The epsilon of double precision floating point has nothing to do with anything since the magnitude of the values and the intermediates is well bellow the precision limits of the representation.

You are correct, however, that if you carry out the computation in pure integer arithmetic you will get the wrong answer:

```
sqlite> select sum(xi)/count(*) from x;
┌──────────────────┐
│ sum(xi)/count(*) │
├──────────────────┤
│ 99947            │
└──────────────────┘
```