I want to get the cumulative average of the following data per month.

EJ

date     val  acum
20221229  1    1
20221230  8    4.5
20221231  3    4
20230101  7    7
20230101  4    5.5

Code:

BEGIN var_out = SELECT FCURR,
    date,
    val,
    SUM(SUM(val)) over (order by date asc) AS acum
    FROM 
(SELECT 
    val
    FROM table
    WHERE "KURST" ='M')
GROUP BY  date,val
ORDER BY 3 DESC; END

CodePudding user response：

You seem to be looking for a window average over months partitions.

In SQL Server, assuming a table like mytable(dt, val):

select dt, val,
   avg(1.0 * val) over(
        partition by year(dt), month(dt) 
        order by dt
    ) running_monthly_avg
from mytable
where kurst = 'M'

This averages the value in monthly partitions that are sorted by date. Typically, the first row of the month averages over itself only, while the last row averages the whole month.

From the sample data and results I don’t see the need for group by in the query, so I removed it.

Here is a DB Fiddle: