Not sure if this is right, but the following seems to work: SELECT c.mnemonic as SYMBOL, ROUND(SUM((s.quantity_num*1.0/s.quantity_denom)), LENGTH(REPLACE(c.fraction, '1', ''))) as SHARES /* rounding to the number of precision indicated in Commodities */ 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;
Is that what you meant? > On Oct 29, 2016, at 11:13 PM, David T. via gnucash-devel > <[email protected]> wrote: > > >> 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 _______________________________________________ gnucash-devel mailing list [email protected] https://lists.gnucash.org/mailman/listinfo/gnucash-devel
