Cumulative average

Question

I'm looking to make a table with three columns:

A: Week
B: average of the week
C: cumulated average

Here is how my code looks like until now:

SELECT  
SubSQLQuery.DocWeek as "Week",
AVG(SubSQLQuery.AvePayDelay)  as "Average"

from (SELECT
    UPPER(ch.HID) as CodeClient,
    ch.HDOCNO as DocNumber,
    ch.HDOCDATE as DocDate,
    ch.HYEAR as DocYear,
    week(ch.HDOCDATE)-1 as DocWeek,
    ch.HMDATE as PayDate,
    month(ch.HMDATE) as PayMonth,
    (ch.HDUEDATE-ch.HDOCDATE) +0.00 as AvePayDelay

from AC_CHISTO ch

where ch.HFYEAR='2016'
and ch.HMDATE IS NOT NULL
and UPPER(ch.HDBK)='VEN') as SubSQLQuery
GROUP BY SubSQLQuery.DocWeek

And this is the result:

What I'm looking for is to determine, for column C, the cumulated average for each week. So for week 4, I need to get the average from week 1 to week 4.

Thanks for help.

Cumulative average

2 Answers