0

How can I gradually add calculated field results from previous rows? E.g.,

row A = 1 + (row A column 1 value * row A column 2 value)
row B = row A + (row B column 1 value * row B column 2 value)
row C = row B + (row C column 1 value * row C column 2 value)

I've been trying for quite some time to work this logic out in Postgres. Here is my pseudocode:

select
    case 
        when row_number() = 1 then 1 + (colA * colB) as a
        else (lag(a) partition by... ) + (colA * colB)
    end as new_value
from
    ...

Essentially, I think I need a lag function to get previous calculated results, but value is held for the next row in field new_value. So then I need to set the next row to pass in new_value to the equation, and so on, recursively.

EDIT:

row A col_3 = if (row A col_1 <= 1 then 1), else if (row A col_1 = 2 then 6), else  1 *  col_2
row B col_3 = if (row B col_1 <= 1 then 1), else if (row B col_1 = 2 then 6), else  row A *  col_2
row C col_3 = if (row C col_1 <= 1 then 1), else if (row C col_1 = 2 then 6), else  row B *  col_2
user3871
  • 12,432
  • 33
  • 128
  • 268
  • Does this answer your question? [Calculating Cumulative Sum in PostgreSQL](https://stackoverflow.com/questions/22841206/calculating-cumulative-sum-in-postgresql) – Bergi Jun 23 '21 at 02:23
  • 2
    Simply `SELECT 1+SUM(colA * colB) OVER (ORDER BY …) FROM …` – Bergi Jun 23 '21 at 02:24
  • @Bergi wouldn't 1 be added to each row's calculation? – user3871 Jun 23 '21 at 02:31
  • 1
    Yes, that's what you want - you start at 1 and then add up the values for each row? It's not `SUM(1+colA*colB)`=`SUM(1)+SUM(colA*colB)` that would add a row count. – Bergi Jun 23 '21 at 02:35
  • @Bergi Thanks so much for this. One more question, how would you handle the above edit, where you're multiplying result from previous row if not the first row, in a conditional – user3871 Jun 23 '21 at 03:20

0 Answers0