Select Latest Available Value Sql

Below is a test table for simplification of what I am looking to achieve in a query. I am attempting to create a query using a running sum which inserts into column b that last sum

Solution 1:

In Postgres you can also use the window function of SUM for a cummulative sum.

Example:

createtable test (a int, b int);
insertinto test (a,b) values (1,null),(2,1),(3,3),(4,null),(5,5),(6,null);

select a, sum(b) over (order by a, b) as "sum"
from test;

 a |  sum
-- | ----
 1 | null
 2 |    1
 3 |    4
 4 |    4
 5 |    9
 6 |    9

db<>fiddle here

And if "a" isn't unique, but you want to group on a? Then you could use a suminception:

select a, sum(sum(b)) over (orderby a) as "sum"
from test
groupby a