Jerin Varghese - 1 month ago 7

SQL Question

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!

Answer

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

Source (Stackoverflow)

Comments