I have a table in mysql with data looking like this example.
x
|ColA |ColB|
|:---- |:------:|
|A1 |B1 |
|A2 |B2 |
|A3 |B3 |
|A4 |B4 |
I want to calculate a columnn colc using the formula:
C1 = A1*B1;
C2 = (C1+A2)*B2;
C3 = (C2+A3)*B3;
C4 = (C3+A4)*B4;
…
ColA ColB ColC
1 5 5
2 6 42
3 7 315
4 8 2552
Advertisement
Answer
You can use a recursive CTE:
with recursive to_r as (select row_number() over (order by t.ColA) r, t.* from test_table t),
cte as (
select t.r, t.ColA*t.ColB p from to_r t where t.r = 1
union all
select c.r+1, t1.ColB*(t1.ColA+c.p) from cte c join to_r t1 on t1.r = c.r+1
)
select p from cte;
See demo here.