InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comWed, 03 Dec 2008 16:25:18 GMT20http://www.teradata.com/teradataforum/Topic11624-9-1.aspxA small change in the above query..select (-1**dt3.c3) * dt1.c1*dt2.c2 as product_col from((select COALESCE(exp(sum(ln(abs(a)))),1) from au.dh where a <0) dt1 (c1)join (select exp(sum(ln(a))) from au.dh where a > 0) dt2 (c2)on 1=1join(select count(a) from au.dh where a<0) dt3 (c3)on 1=1)Thu, 24 Jul 2008 02:10:19 GMTdhirajpalsehttp://www.teradata.com/teradataforum/Topic11624-9-1.aspxHi,i created a table 'dh' in 'au' database with column name as 'A' of integer data type.Data in this column is as follows.A-10-9-612345I wrote the query, which is just an enhancement of Carlos's query.select (-1**dt3.c3) * dt1.c1*dt2.c2 as product_col from((select exp(sum(ln(abs(a)))) from au.dh where a <0) dt1 (c1)join (select exp(sum(ln(a))) from au.dh where a > 0) dt2 (c2)on 1=1join(select count(a) from au.dh where a<0) dt3 (c3)on 1=1)this gives the product of the column data by taking care of the signs of numbers.output with the given data is -64800.00hope this will help u.Thu, 29 May 2008 03:38:57 GMTdhirajpalsehttp://www.teradata.com/teradataforum/Topic11624-9-1.aspxHi Carlos,Firstly thanks a lot for your reply.I also tried the same and it is working.sel cast (EXP(SUM(LN(abs(dept_count)))) as decimal ) *(case when ((-1 *sum(case when dept_count<0 then 1 else 0 end)) mod 2 )=0then 1else -1end) as COL_PROD from dept;Table DeptDept_Name Dept_CountA 1B -2 C -3Thu, 29 May 2008 03:35:43 GMTGaurav_Mishrahttp://www.teradata.com/teradataforum/Topic11624-9-1.aspxHi again>>"Then product should have to be -20 but using log it won't work.>>Using ABS(absolute) will give you the product but will supress>>the sign in the Product value.>>Any suggestions on this?"Try: SELECT (case when sum(case when dept_count < 0 then 1 else 0 end) mod 2 = 1 then -1 else 1 end) * EXP(SUM(LN(ABS(dept_count)))) FROM dept_countHope this helps.Cheers.CarlosThu, 29 May 2008 03:03:34 GMTCarlosALhttp://www.teradata.com/teradataforum/Topic11624-9-1.aspxHi Carlos,What if we need the sign also in the product?Say if the values are like 1-45Then product should have to be -20 but using log it won't work.Using ABS(absolute) will give you the product but will supress the sign in the Product value.Any suggestions on this?Wed, 28 May 2008 22:29:56 GMTGaurav_Mishrahttp://www.teradata.com/teradataforum/Topic11624-9-1.aspxhi carlos,just browsing thru the posts and your solution is an interesting way of using the available functions effectivily.....thats cool!Wed, 28 May 2008 11:26:44 GMTSNhttp://www.teradata.com/teradataforum/Topic11624-9-1.aspxHi.This is my first answer to any post here (I'm quite new to Teradata, big DB background though).Recalling from my maths: 1. logarithm converts products in sums.2. logarithm and exponentiation are inverse functions.SoSELECT EXP(SUM(LN(dept_count))) from Dept_Count should give you the answer.Cheers.Carlos.Wed, 28 May 2008 06:33:35 GMTCarlosALhttp://www.teradata.com/teradataforum/Topic11624-9-1.aspxDear All,Is their any way to perform the multiplication of all the values for a column using recursion.Regards,Gaurav.e.gTable Dept_CountDept_Name Dept_CountIT 2Finance 3Treasurey 5CS 1 The product of all the records for Dept_Count Col should have to be 30(2*3*5*1)Wed, 28 May 2008 01:50:02 GMTGaurav_Mishra