From: --CELKO-- on 14 Jan 2010 20:37 Here is a version of the aggregate product function in SQL. You will need to have the logarithm and exponential functions. The idea is that there are three special cases - all positive numbers, one or more zeros, and some negative numbers in the set. You can find out what your situation is with a quick test on the sign () of the minimum value in the set. Within the case where you have negative numbers, there are two sub- cases: (1) an even number of negatives or (2) an odd number of negatives. You then need to apply some High School algebra to determine the sign of the final result. SELECT CASE MIN (SIGN(nbr)) WHEN 1 THEN EXP(SUM(LN(nbr))) -- all positive numbers WHEN 0 THEN 0.00 -- some zeroes WHEN -1 -- some negative numbers THEN (EXP(SUM(LN(ABS(nbr)))) * (CASE WHEN MOD (SUM(ABS(SIGN(nbr)-1)/ 2)), 2) = 1 THEN -1.00 ELSE 1.00 END) ELSE NULL END AS big_pi FROM Foobar; Or another way; SELECT CASE MIN(ABS(SIGN(nbr))) WHEN 0 THEN 0.00 -- some zeroes ELSE -- no zeroes EXP(SUM(LOG(ABS(NULLIF(nbr, 0))))) * CASE WHEN MOD (CAST(SUM(ABS(SIGN(nbr)-1)/2) AS INTEGER), 2) = 1 THEN -1.00 ELSE 1.00 END END AS big_pi As an aside, the book BYPASSES:A SIMPLE APPROACH TO COMPLEXITY by Z. A. Melzak (Wiley-Interscience, 1983, ISBN 0-471-86854-X), is short mathematical book on the general principle of conjugacy. This is the method of using a transform and its inverse to reduce the complexity of a calculation. From: Tom Cooper on 14 Jan 2010 21:08 Once again, you are ignoring the fact that this is a Microsoft SQL Server usergroup. The correct function is LOG(). LN() is not a valid function in Microsoft SQL Server. Tom "--CELKO--" wrote in message news:bcdeb277-41a4-4cf4-be73-85fc01a00c87(a)m26g2000yqb.googlegroups.com...> Here is a version of the aggregate product function in SQL. You will > need to have the logarithm and exponential functions. The idea is that > there are three special cases - all positive numbers, one or more > zeros, and some negative numbers in the set. > > You can find out what your situation is with a quick test on the sign > () of the minimum value in the set. > > Within the case where you have negative numbers, there are two sub- > cases: (1) an even number of negatives or (2) an odd number of > negatives. You then need to apply some High School algebra to > determine the sign of the final result. > > SELECT CASE MIN (SIGN(nbr)) > WHEN 1 THEN EXP(SUM(LN(nbr))) -- all positive numbers > WHEN 0 THEN 0.00 -- some zeroes > WHEN -1 -- some negative numbers > THEN (EXP(SUM(LN(ABS(nbr)))) > * (CASE WHEN > MOD (SUM(ABS(SIGN(nbr)-1)/ 2)), 2) = 1 > THEN -1.00 ELSE 1.00 END) > ELSE NULL END AS big_pi > FROM Foobar; > > Or another way; > > SELECT CASE MIN(ABS(SIGN(nbr))) > WHEN 0 THEN 0.00 -- some zeroes > ELSE -- no zeroes > EXP(SUM(LOG(ABS(NULLIF(nbr, 0))))) > * CASE WHEN MOD (CAST(SUM(ABS(SIGN(nbr)-1)/2) AS INTEGER), > 2) = 1 > THEN -1.00 ELSE 1.00 END > END AS big_pi > > As an aside, the book BYPASSES:A SIMPLE APPROACH TO COMPLEXITY by Z. > A. Melzak (Wiley-Interscience, 1983, ISBN 0-471-86854-X), is short > mathematical book on the general principle of conjugacy. This is the > method of using a transform and its inverse to reduce the complexity > of a calculation. First  |  Prev  |