Аggregation of the result in different years in a new column.Oracle

Тhis is the result i achieved on a monthly basis.

Here I collected all the results on a monthly basis In Comulative. Еach new period is added to the old periods in a cumulative

SELECT date,period,SUM(period) OVER(ORDER BY date ) AS Comulative) group by date

date,        period     cumulative  
1-May-19,      1,      1    
1-Jun-19,      2,      3    
1-Jul-19,      4,      7    
1-Aug-19,      1,      8    
1-Sep-19,      0,      8    
1-May-20,      10,     18   
1-Feb-20,      0,     18    
1-Mar-20,      0,     18    
1-Apr-21,      1,     19    
1-Jul-21,      1,     20    
1-Aug-21,      1,     21    
1-Sep-21,      1,     22

I want to achieve the same result on an annual basis.

date,         period, cumolative 
2019,            8,     8
2020,            10,     18
2021,             4,     22

I use this to format the date. to_char(date,'yyyy')

Solution

You can first generate the SUM result and then can do a cumulative sum -

SELECT date, period, SUM(period) OVER(ORDER BY date)
  FROM (SELECT EXTRACT(YEAR FROM date) date, SUM(period) period
          FROM YOUR_TABLE
         GROUP BY EXTRACT(YEAR FROM date));