SQL - How to sum only one row above

Question

I have a data set like this one below:

The result column is the sum of the value column of this row and the value column one row above the current row.

Is it possible to write a query for this result?

Answer 1

If your database does not support LAG, then we have some other options. In MySQL, we can try using a correlated subquery to find the lag value:

SELECT
    id,
    value,
    COALESCE((SELECT t2.value FROM yourTable t2
     WHERE t2.id < t1.id ORDER BY t2.id DESC LIMIT 1), 0) result
FROM yourTable t1;

A similar query would also work on SQL Server, using TOP 1 in the subquery.