monthly running average

[dharkangel]

Hi everyone, I have a table that has a list of about 15 weeks worth of data. column 1 is displays the weekdate (1/7/2006), column two is an integer that displays the number of actions that happen that week. So far I have data from January to the present. My question is, is there a way to get the Monthly Running Average of this data using pure SQL? For example, here is some real data in my db:

WeekDate NumberOfActions
_________ _______________
1/7/2006 3
1/14/2006 5
1/21/2006 0
1/28/2006 2
2/4/2006 6
2/11/2006 3
2/18/2006 2
2/25/2006 1
3/4/2006 4

So my new column would look like this:

RunningAvg
3
4
2.67
2.5
6
4.5
3.67
3
4
----------------
I know the logic I need to do, but don't know how to translate it into sql.

thank you,

 

[Golom]

Try

Select A.WeekDate, A.NumberOfActions,

       SUM(B.NumberOfActions) / Count(*) As RunningAvg

From MyTable A INNER JOIN myTable B
     ON A.WeekDate >= B.WeekDate

Group By A.WeekDate, A.NumberOfActions

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Golom]

Oops ... sorry ... that should be

Select A.WeekDate, A.NumberOfActions,

       SUM(B.NumberOfActions) / Count(B.WeekDate) As RunningAvg

From MyTable A INNER JOIN myTable B
     ON A.WeekDate >= B.WeekDate

Group By A.WeekDate, A.NumberOfActions

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[lespaul]

you're an "oops" kinda guy today! That's the second one in a row I've looked at where you've done that!

les

 

[Golom]

As I said in the other thread "... if only I could read ..."

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]