Jerin Varghese - 1 year ago 67
SQL Question

# SQL Multiply values from all previous rows

Question - Multiply previous rows

Refer to the image above.

I have Table 1, and I want to produce Table 2 with SQL.

The first year has to be set to the value 10. The values following will multiply 10 by the multiplier for that year and previous years in Table 1.

For example:

• For 2002, the value will be 10 * 2 (2002 multiplier) * 1 (2001 multiplier) = 20.

• For 2005, the value will be 10 * 5 * 3 * 1 * 2 * 1 (all previous year multipliers) = 300.

How would I go about doing this? I'd appreciate any help. Thanks!

A colleague of mine long ago taught me a trick to solve this kind of problems using logarithm properties.

Basically you can do:

`````` Exp(sum(ln(multiplier)))
``````

Edited after the OP made me realize it was incomplete

To do the cumulative logic you need you should apply this on a self-join

`````` select a.youryear, Exp(sum(ln(b.multiplier))) cumulative_mutiplier
from yourtable as a
join
yourtable as b on a.youryear>=b.youryear
group by a.youryear;
``````

I've prepared a test on rextester

`````` create table yourtable (
youryear integer,
multiplier integer
);

insert into yourtable(youryear,multiplier) values (2000,10);
insert into yourtable(youryear,multiplier) values (2001,1);
insert into yourtable(youryear,multiplier) values (2002,2);
insert into yourtable(youryear,multiplier) values (2003,1);
insert into yourtable(youryear,multiplier) values (2004,3);
insert into yourtable(youryear,multiplier) values (2005,5);

select a.youryear, Exp(sum(ln(b.multiplier))) cumulative_mutiplier
from yourtable as a
join
yourtable as b on a.youryear>=b.youryear
group by a.youryear;
``````

The result is:

``````    youryear    cumulative_mutiplier
1  2000    10
2  2001    10
3  2002    20
4  2003    20
5  2004    60
6  2005    300
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download