Progressive Average 1

[secureshell]

Hi,

I have a table with the following fields & values:

ID Weight
1 10
1 15
1 20
2 18
2 22
2 25

I need to display the progressive average of the 'Weight' field for each record grouped by ID, e.g;

ID Weight Avg
1 10 10.0 -> (10/1)
1 15 12.5 -> [(10+15)/2]
1 20 15.0 -> [(10+15+20)/3]
2 18 18.0 -> (18/1)
2 22 20.0 -> [(18+22)/2]
2 25 18.33 -> [(18+22+25)/3]

The Avg colum is the new colum that needs to be created and that will display the progressive 'Weight' averages. Any help would be great. I have considered alternatives already for this issue but doing it through SQL is much much clean. I am using Access 2000 and queriying from VB. Thx {W r i t e C o d e A n d K i c k A s s}

 

[DanJR]

Hi there,

Are you ordering by ID then by weight(?)...if so,

does this help...

SELECT A.ID, A.Weight, (SELECT Avg([Weight]) FROM myWeights AS B WHERE (B.ID = A.ID AND B.Weight <= A.Weight)) AS Ave
FROM myWeights AS A
ORDER BY A.ID, A.Weight

Cheers,
Dan

 

[secureshell]

Thx DanJR. Your query is working fine if I were to order it by ID and then weight. But this is what I forgot to mention in my initial post. There is a date column as well and basically the way I would like to order it would be by ID and then date. The weights in each record could be a random number, I just want to get the progressive average in my recordset.

I am slightly trying to modify your query at the moment to see if I can get the results I want. {W r i t e C o d e A n d K i c k A s s}

 

[DanJR]

hey secureshell,

This should order by date instead of weight:

SELECT A.ID, A.Weight, (SELECT Avg([Weight]) FROM myWeights AS B WHERE (B.ID = A.ID AND B.Date <= A.Date)) AS Ave
FROM myWeights AS A
ORDER BY A.ID, A.Date

Cheers,
Dan

 

[secureshell]

Works sweet so far DanJR. Thx for your help. {W r i t e C o d e A n d K i c k A s s}