Running average - subquery?

[Maurice]

Hi Guys,

Been struggling a bit on this so a pointer would be great.
Just a simple example would do..

tableA with two fields "Startdate" and "Amount"

now i need a running average in the query which would give me the following
output:

[Startdate] - [amount] - [average]
1-1-2010 - 100 - 100
2-1-2010 - 50 - 75
3-1-2010 - 60 - 70

So in the average column the last value should be the average of the sum of
the previous records and the current records (being the average of the total).

I know that the fieldnames are incorrect they are just for illustrating the
question.
any pointers appreciated.

 

[Marshall Barton]

Been struggling a bit on this so a pointer would be great.
Just a simple example would do..

tableA with two fields "Startdate" and "Amount"

now i need a running average in the query which would give me the following
output:

[Startdate] - [amount] - [average]
1-1-2010 - 100 - 100
2-1-2010 - 50 - 75
3-1-2010 - 60 - 70

So in the average column the last value should be the average of the sum of
the previous records and the current records (being the average of the total).

You can use a subquery to calculate the running average
field:
Average: (SELECT Avg(X.amount) FROM table As X
WHERE X.Startdate <= table.startdate)

 

[Daryl S]

Maurice -

Try this:

SELECT TableA.Startdate, TableA.amount, (SELECT Sum(amount) from TableA AS
T1 WHERE T1.Startdate <= TableA.Startdate)/(SELECT Count(amount) from TableA
AS T1 WHERE T1.Startdate <= TableA.Startdate) AS RunningAvg
FROM TableA
ORDER BY TableA.Startdate;

 

[KARL DEWEY]

Try this with your table and field names --
SELECT Aaron.SalesDate, Aaron.Qty, (SELECT Sum([XX].Qty)/ Count([XX].QTY)
FROM Aaron AS [XX] WHERE [XX].SalesDate <= Aaron.SalesDate) AS [Average]
FROM Aaron
ORDER BY Aaron.SalesDate;

 

[Maurice]

Daryl, Karl and Marsh,

Thanx for the reponse and pointers, got it up and running as i would like it
to be.
much appreciated
--
Maurice Ausum

Been struggling a bit on this so a pointer would be great.
Just a simple example would do..

tableA with two fields "Startdate" and "Amount"

now i need a running average in the query which would give me the following
output:

[Startdate] - [amount] - [average]
1-1-2010 - 100 - 100
2-1-2010 - 50 - 75
3-1-2010 - 60 - 70

So in the average column the last value should be the average of the sum of
the previous records and the current records (being the average of the total).

You can use a subquery to calculate the running average
field:
Average: (SELECT Avg(X.amount) FROM table As X
WHERE X.Startdate <= table.startdate)