Thread: How to sum monetary variables
I'm building a table (which is a report that has to be printed) with a bunch of items (up to 300 in some cases) that have unitary price (stored in a numeric(9,2) field), how many there are, and the total price for each item. At the end of the table there is a total of all the items. The app is running on PHP and PG is the backend. The question is, how do I get the total of everything? Running it on PHP gives one value, doing a sum() on the backend gives another, and I'm starting to notice that even using python as a calculator gives me errors (big ones). Right now I'm doing the maths by hand to find out who has the biggest error, or if any is 100% accurate. Any ideas? -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador
2011/7/18 Martín Marqués <martin.marques@gmail.com>: > I'm building a table (which is a report that has to be printed) with a > bunch of items (up to 300 in some cases) that have unitary price > (stored in a numeric(9,2) field), how many there are, and the total > price for each item. At the end of the table there is a total of all > the items. > > The app is running on PHP and PG is the backend. > > The question is, how do I get the total of everything? > > Running it on PHP gives one value, doing a sum() on the backend gives > another, and I'm starting to notice that even using python as a > calculator gives me errors (big ones). Right now I'm doing the maths > by hand to find out who has the biggest error, or if any is 100% > accurate. I wouldn't even bother testing client implementations that are using floating point numbers to do the math -- they are going to be wrong. If you snoop around you should find math libraries that handle do a better job handling exact numbers for most popular languages. A few languages, like the hated COBOL, have them built in. merlin
On 19/07/2011 6:04 AM, Merlin Moncure wrote: > I wouldn't even bother testing client implementations that are using > floating point numbers to do the math -- they are going to be wrong. > If you snoop around you should find math libraries that handle do a > better job handling exact numbers for most popular languages. You'll also need to convince your database interface to use the decimal library you choose, or get it to return 'numeric' types as strings so you can convert them with your chosen library yourself. It'll do you no good to convert from Pg numeric -> floating point -> exact decimal library format. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/
2011/7/18 Merlin Moncure <mmoncure@gmail.com>: > 2011/7/18 Martín Marqués <martin.marques@gmail.com>: >> I'm building a table (which is a report that has to be printed) with a >> bunch of items (up to 300 in some cases) that have unitary price >> (stored in a numeric(9,2) field), how many there are, and the total >> price for each item. At the end of the table there is a total of all >> the items. >> >> The app is running on PHP and PG is the backend. >> >> The question is, how do I get the total of everything? >> >> Running it on PHP gives one value, doing a sum() on the backend gives >> another, and I'm starting to notice that even using python as a >> calculator gives me errors (big ones). Right now I'm doing the maths >> by hand to find out who has the biggest error, or if any is 100% >> accurate. > > I wouldn't even bother testing client implementations that are using > floating point numbers to do the math -- they are going to be wrong. > If you snoop around you should find math libraries that handle do a > better job handling exact numbers for most popular languages. A few > languages, like the hated COBOL, have them built in. > All my testing has suggested that Perl number addition, etc. is arbitrary precision safe. (I usually use additional libraries for finer control though). I don't know about PHP. Best Wishes, Chris Travers
BTW, since this is numeric() I would trust the Pg backend before I would trust the client languages. Best Wishes, Chris Travers