How can I sum the previous row with LAG() function?

Edisson Gabriel López

Best regards,

I am using the following function (LAG) to sum the previous row: 

(LAG(INV_FINAL, 1, 0) OVER (PARTITION BY DISTRIBUIDORA ORDER BY PERIODO ASC) + LAG(INV_FINAL, 2, 0) OVER (PARTITION BY DISTRIBUIDORA ORDER BY PERIODO ASC)) + INV_FINAL AS RESULT

But it is not giving me the expected result as can you see:

The correct result should be :

I am attentive to your comments, I would be very grateful if you can help me, thank you.

Paulzip

You probably want something like this (untested as not near DB) :

sum(result) over (partition by distribuidora order by periodo asc rows between unbounded preceding and current row) rows_sum

Paulzip

You probably want something like this (untested as not near DB) :

sum(result) over (partition by distribuidora order by periodo asc rows between unbounded preceding and current row) rows_sum

Frank Kulash

Hi, @Edisson Gabriel López

Whenever you have a question, please post CREATE TABLE and INSERT statements for a little sample data, so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

Edisson Gabriel López

Hi @Paulzip ,

https://community.oracle.com/tech/developers/discussion/comment/16811443#Comment_16811443

You are amazing. I didn't know about it, it's very new to me, thank you very much.

sum(INV_FINAL) OVER (PARTITION BY DISTRIBUIDORA ORDER BY PERIODO ASC rows between unbounded preceding and current row) RESULT

Do you have any documentation where I can find all these types of functions, I would be very grateful.

Thanks to you

Frank Kulash

Do you have any documentation where I can find all these types of functions,

All built-in functions are documented in the SQL Language Reference. For example:

SUM (oracle.com)

cormaco

Here is an introduction zu to Analytic Functions:

https://oracle-base.com/articles/misc/analytic-functions