If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Running average - subquery?
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. -- Maurice Ausum |
#2
|
|||
|
|||
Running average - subquery?
Maurice wrote:
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) -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Running average - subquery?
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; -- Daryl S "Maurice" wrote: 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. -- Maurice Ausum |
#4
|
|||
|
|||
Running average - subquery?
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; -- Build a little, test a little. "Maurice" wrote: 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. -- Maurice Ausum |
#5
|
|||
|
|||
Running average - subquery?
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 "Marshall Barton" wrote: Maurice wrote: 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) -- Marsh MVP [MS Access] . |
Thread Tools | |
Display Modes | |
|
|