Running Multiplication

Stupe · #1 (**permalink**) 02-21-06, 21:39

Hi all

I need a column, that is a Running Multiplication of it, and another column, sort of like Running Sum. Sample output:

Code:

RowNum	Count	RunProd
1	2	2
2	3	6
3	4	24
4	5	120

Would anyone have any idea if this can be done in DB2, purely using SQL?

sathyaram_s · #2 (**permalink**) 02-21-06, 21:54

I'm not sure what you mean by 'purely using SQL?' ... Do you already have n number of records in the table with all columns populated and now for every insert into the table you want to populate runprod column ? or you have all nulls in runprod now and want to update it with SQL?

Cheers
Sathyaram

Stupe · #3 (**permalink**) 02-21-06, 22:25

Hey Sathyaram

Say I already have a table, with the columns RowNum and Count. I need to come up with a query, that populates RunProd. Don't want to be updating or populating any columns, just need to see the numbers.

Any clearer?

sathyaram_s · #4 (**permalink**) 02-22-06, 03:36

Use a recursive SQL statement ...

The following works even if there are gaps in rownum

HTH

Sathyaram

Code:

create table tm1(rownum int,count int)
insert into tm1 values(1,2),(2,3),(3,4),(4,5)
with xtemp as 
(
select rownumber() over (order by rownum) as xt_rid,rownum as xt_rownum,count as xt_count from tm1 
),
temp(t_rid,t_rownum,t_count,runprod) as 
(
select xt_rid,xt_rownum,xt_count,xt_count from xtemp where xt_rid=1
union all
select xt_rid,xt_rownum,xt_count,runprod*xt_count from xtemp,temp where xt_rid=t_rid+1 
)
select t_rownum as rownum,t_count as count,runprod from temp

Stupe · #5 (**permalink**) 03-29-06, 04:12

Sathyaram,

Thanks for the solution. Worked like a charm.

Rob den Heijer · #6 (**permalink**) 03-31-06, 05:46

yeah... or
with x (id, val, prd) as (
values (1,2,2)
union all
select id+1, val+1, prd*(val+1)
from x
where id+1 <= 10
)
select * from x

modify to what you want!

Internet Services

Web Development

Digital Marketing

Consumer Tech