> On Oct 29, 2016, at 9:44 PM, John Ralls <[email protected]> wrote: > > >> On Oct 29, 2016, at 9:16 AM, David T. <[email protected]> wrote: >> >> John, >> >> Probably. As in, probably I did something wrong. (What else is new? ) >> >> When I first queried the database, it returned only integers when I >> calculated the shares, so I Googled to find out how to get some decimals. My >> solution was to multiply by 1.0, which yielded the aforementioned results. >> >> Specifically, my query included SUM (shares*1.0/shares_denom). This resulted >> in the residuals. >> >> So, what is the "rational" way to calculate the shares? >> > > David, > > Rational math is what you learned in primary school: Find the least common > denominator, convert every fraction to be expressed with that denominator, > then add the numerators and simplify the sum. Always use integers, no decimal > points. > > The reason that's necessary is that 1/10 isn't exactly representable in > binary, so for the computer 0.1 + 0.9 - 1.0 != 0.0 at infinite precision. > The error accumulates and is larger when more decimal places are involved so > for more complex calculations the error can become large enough to display, > as you discovered. > > Regards, > John Ralls
OK, but my question remains. In SQLite3, if I use the following: SELECT c.mnemonic as SYMBOL, SUM((s.quantity_num/s.quantity_denom)) as SHARES /* Note the splits fields used without any treatment */ FROM accounts as a, commodities as c, splits as s WHERE (a.account_type='MUTUAL' OR a.account_type='STOCK') AND a.guid=s.account_guid AND a.commodity_guid=c.guid GROUP BY c.mnemonic ORDER BY SHARES; I get ONLY integral results for SHARES. A search online tells me that if I use: SELECT c.mnemonic as SYMBOL, SUM((s.quantity_num*1.0/s.quantity_denom)) as SHARES /* Note the splits fields used with treatment to force float */ FROM accounts as a, commodities as c, splits as s WHERE (a.account_type='MUTUAL' OR a.account_type='STOCK') AND a.guid=s.account_guid AND a.commodity_guid=c.guid GROUP BY c.mnemonic ORDER BY SHARES; Then, I get decimals, but also these residuals. So, please tell me what exactly I should do to get an accurate accounting of the shares in the file? TIA, David _______________________________________________ gnucash-devel mailing list [email protected] https://lists.gnucash.org/mailman/listinfo/gnucash-devel
