|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/2/2008 8:03:51 AM
Posts: 3,
Visits: 17
|
|
Dear All,
Is their any way to perform the multiplication of all the values for a column using recursion.
Regards,
Gaurav.
e.g
Table Dept_Count
Dept_Name Dept_Count
IT 2
Finance 3
Treasurey 5
CS 1
The product of all the records for Dept_Count Col should have to be 30(2*3*5*1)
Gaurav Mishra.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/29/2008 6:03:37 AM
Posts: 10,
Visits: 166
|
|
Hi.
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.
So
SELECT EXP(SUM(LN(dept_count))) from Dept_Count should give you the answer.
Cheers.
Carlos.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 8/11/2008 3:00:36 PM
Posts: 76,
Visits: 147
|
|
hi carlos,
just browsing thru the posts and your solution is an interesting way of using the available functions effectivily.....thats cool!
-SN
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/2/2008 8:03:51 AM
Posts: 3,
Visits: 17
|
|
Hi Carlos,
What if we need the sign also in the product?
Say if the values are like
1
-4
5
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?
Gaurav Mishra.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/29/2008 6:03:37 AM
Posts: 10,
Visits: 166
|
|
Hi 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_count
Hope this helps.
Cheers.
Carlos
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/2/2008 8:03:51 AM
Posts: 3,
Visits: 17
|
|
Hi 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 )=0
then 1
else -1
end)
as COL_PROD from dept;
Table Dept
Dept_Name Dept_Count
A 1
B -2
C -3
Gaurav Mishra.
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 9/4/2008 6:57:31 AM
Posts: 28,
Visits: 153
|
|
Hi,
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
-6
1
2
3
4
5
I 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=1
join
(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.00
hope this will help u.
Dhiraj P
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 9/4/2008 6:57:31 AM
Posts: 28,
Visits: 153
|
|
A 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=1
join
(select count(a) from au.dh where a<0) dt3 (c3)
on 1=1
)
Dhiraj P
|
|