SQL - Add all previous columns and current column without lag and lead

Question

I got a table like

  ID      YEAR    VALUE  
  ----------------------
  1       2017    1
  1       2018    8
  1       2019    0
  1       2020    6
  1       2021    2

i'm trying to add all previous row VALUE column values to the current row VALUE column value and display a total. My result should be as follows:

  ID      YEAR    VALUE  TOTAL
  ----------------------------
  1       2017    1      1
  1       2018    8      9
  1       2019    0      9
  1       2020    6      15
  1       2021    2      17

i tried using join and row_number to get this but 2019 value of 0 is messing up.

 select *, row_number() over (partition by ID order by YEAR) as RN
 into #t1
 from tbl

 select 
    t1.VALUE + ISNULL(t2.VALUE, 0),
    t1.*
from #t1 as t1
left join #t1 as t2 on t2.ID = t1.ID and (t2.RN + 1) = t1.RN

the result that I'm getting with this query is:

  ID      YEAR    VALUE  TOTAL
  ----------------------------
  1       2017    1      1
  1       2018    8      9
  1       2019    0      8
  1       2020    6      6
  1       2021    2      8

Any help please?

Answer 1

If you want a solution similar to the one you tried:

select *, row_number() over (partition by ID order by YEAR) as RN
into #t1
from tablename;

select
  t.id, t.year, t.value, sum(#t1.value) TOTAL
from tablename t inner join #t1
on #t1.id = t.id and #t1.year <= t.year
group by t.id, t.year, t.value;

Or calculate the running total with a subquery:

select t.*,
  (select sum(value) from tablename where id = t.id and year <= t.year) TOTAL
from tablename t 

See the demo.
Results:

> id | year | value | TOTAL
> -: | ---: | ----: | ----:
>  1 | 2017 |     1 |     1
>  1 | 2018 |     8 |     9
>  1 | 2019 |     0 |     9
>  1 | 2020 |     6 |    15
>  1 | 2021 |     2 |    17