Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    21

    Unanswered: Running Multiplication

    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?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2004
    Posts
    21
    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?

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Last edited by sathyaram_s; 02-22-06 at 05:55.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Feb 2004
    Posts
    21
    Sathyaram,

    Thanks for the solution. Worked like a charm.

  6. #6
    Join Date
    Feb 2006
    Location
    Utrecht, Netherlands
    Posts
    16
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •