Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Instead of SUM() I require MULTIPLY
Jonathan, your soution is pretty clever, and actually more useful than
you seem to think.
The shortcomings you mentioned aren't insurmountable.
What you need to do is:
From these, computing the final product is easy -- I won't waste bits on the details.
Sadly, there remains one minor shortcoming that is not so easy to fix. The final result is only APPROXIMATE! We are performing some fairly complex floating-point operations to approximate what should (or a least *could*) be an integer computation. For example, when I loaded up a test table with the values 2, 2, 2, 3, -1, -1, -1, 0, -1 I came up with these results:
MAGNITUDE: 24.00000000000000000000000000000000000005 NEGATIVES: 3 ZEROES: 1
Not bad really -- there is definitely a rounding error, but it's pretty minor. I suspect this error will increase according to something like the number of distinct values in the table; it could actually become significant over a sufficiently large amount of data. Happily, 39 signficant (decimal) digits go a *long* way; this is not nearly so awful as doing binary floating-point operations with (something like) a 23-bit mantissa...
Jonathan Gennick wrote:
>A caveat to my previous response. That trick works only for positive
>numbers. It'll fail if a zero or negative value is encountered.
>
>Best regards,
>
>Jonathan Gennick --- Brighten the corner where you are
>http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
>
>
>
>Friday, December 9, 2005, 5:13:09 PM, Ethan Post (post.ethan_at_gmail.com) wrote:
>EP> I just came up with a function I would like, but don't think exists.
>
>EP> TABLE TEST (X NUMBER)
>EP> =====================
>EP> 2
>EP> 2
>EP> 4
>
>EP> select sum(x) from test;
>
>EP> will return 8...
>
>EP> what I need is
>
>EP> select multiply(x) from test;
>
>EP> will return 16, because 2*2*4 is 16.
>
>EP> Anyone ever seen a SQL aggregate function like this? I don't think it exists
>EP> but I hold out hope.
>
>EP> - Ethan
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 09 2005 - 21:21:40 CST