Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-28-2008
01:50 AM

05-28-2008
01:50 AM

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)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-28-2008
06:33 AM

05-28-2008
06:33 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-28-2008
11:26 AM

05-28-2008
11:26 AM

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!

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-28-2008
10:29 PM

05-28-2008
10:29 PM

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?

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-29-2008
03:03 AM

05-29-2008
03:03 AM

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

>>"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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-29-2008
03:35 AM

05-29-2008
03:35 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-29-2008
03:38 AM

05-29-2008
03:38 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-24-2008
02:10 AM

07-24-2008
02:10 AM

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

)

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

)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-19-2012
11:21 PM

06-19-2012
11:21 PM

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