accumulated average sql

Question

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

Answer 1

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:

dt	val	running_monthly_avg
2022-12-29	1	1.000000
2022-12-30	8	4.500000
2022-12-31	3	4.000000
2023-01-01	7	7.000000
2023-01-02	4	5.500000