An SQL chalenge

Question

I would like to solve this with pure SQL sentences in an AMDP method, I could solve this easily in ABAP, even in an AMDP method using loops, but as I said, I would like to solve this with SQL sentences, without use any kind of loops.

Please take a look on this image:

I have 2 columns, the first I'll name as D and the second as E

The D column, is a result of a SELECT SUM, but the E column, is a calculated column, and it should work as follow:

• First line both columns are equal E1 = D1
• In the second line, E2 = E1 + D2
• In the third line, E3 = E2 + D3
• In the forth line, E4 = E3 + D4
• And so on.

So that's it. Is it possible to solve this with pure SQL sentences?

Answer 1

Have the SUM in a subquery, then do a select from that subquery along with a row number. Insert that into a temporary table. Then select from that temporary table and do a join to the same table based on the row number, looking for the previous row.

SELECT D, ROW_NUMBER() AS ROW_NUM
INTO #TempTable
FROM (SELECT SUM() AS D FROM table) a

SELECT
    t1.D
    ,CASE
        t2.D IS NULL THEN t1.D
        ELSE t2.D + t1.D AS E
FROM #TempTable t1
    LEFT JOIN #TempTable t2 ON t1.ROW_NUM = t2.ROW_NUM + 1