Perform Multiplication of all the values for a Column

Database

Perform Multiplication of all the values for a Column

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)
8 REPLIES

Re: Perform Multiplication of all the values for a Column

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.
SN
N/A

Re: Perform Multiplication of all the values for a Column

hi carlos,

just browsing thru the posts and your solution is an interesting way of using the available functions effectivily.....thats cool!

Re: Perform Multiplication of all the values for a Column

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?

Re: Perform Multiplication of all the values for a Column

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

Re: Perform Multiplication of all the values for a Column

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

Re: Perform Multiplication of all the values for a Column

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.

Re: Perform Multiplication of all the values for a Column

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
)
N/A

Re: Perform Multiplication of all the values for a Column

what does this warning mean in teradata-

Warning: 2892 Null value eliminated in set function