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.

Answer 1

If I understood you correctly, you can do this with a correlated query:

  SELECT s.DocWeek,
         s.AverageCol.
         (SELECT AVG((ch2.HDUEDATE - ch2.HDOCDATE) + 0.00) 
          FROM AC_CHISTO ch2
          where ch2.HFYEAR = '2016' and ch2.HMDATE IS NOT NULL
            and UPPER(ch2.HDBK) = 'VEN' and (week(ch2.HDOCDATE) -1) <= (week(s.HDOCDATE) - 1)
           ) as CumAvg
  FROM(
      SELECT week(ch.HDOCDATE) - 1 as DocWeek,
             AVG((ch.HDUEDATE - ch.HDOCDATE) + 0.00) as AverageCol,
      from AC_CHISTO ch
      where ch.HFYEAR = '2016'
            and ch.HMDATE IS NOT NULL
            and UPPER(ch.HDBK) = 'VEN'
      GROUP BY week(ch.HDOCDATE) - 1) s