Query for average of the last 10 records

[charli]

I have a table of Waste IDs, with a relationship with a second table of Waste Weights:
WasteIDs

id  name
1. Paper
2. Cardboard
3. Cans

WasteWeights:

Id   date   kg
1. 1/1/09 12
1. 1/2/09  24
2. 1/1/09  2

I want to look up the last 10 wasteweights for an id, and get an average.
So I can do:

SELECT avg(weight)
FROM (SELECT TOP 10 weight
      FROM wasteweights
      WHERE id=1
      ORDER BY [date] DESC)

to get the last 10, but this only works when i put in particular IDs, I want the average of the top 10 for each ID so I can have it as part of a report
so the final datasheet will be like:

Id   Avg
1. 18
2.  2

Any ideas? Could it be done with multiple queries maybe?

[NeoPa]

I think you'll need multiple queries I'm afraid.

[NeoPa]

No guarantees, but this may work :

SELECT   [ID]
     ,   (SELECT Avg([Weight])
          FROM (SELECT TOP 10
                         [Weight]
                FROM     [WasteWeights] AS Inner
                WHERE    Inner.ID=Outer.ID
                ORDER BY Inner.Date DESC)) AS AvgWeight
FROM     [WasteWeights] AS Outer
GROUP BY [ID]

[charli]

Hmm, I get a 'syntax error in FROM clause', and it highlights the later one but even if you attempt putting the innermost query in, you get the same error

It doesn't like the placement of 'As' or using keywords Inner and Outer, but even once those are altered (which could be breaking its intended use horribly, sorry):

SELECT [streamid], (SELECT Avg([Weight]) as avg  FROM 
(SELECT TOP 10  [Weight] as inn  FROM  [WasteWeights]  WHERE  Inn.streamid=Out.streamID ORDER BY Inn.Date DESC)
AS AvgWeight) AS Out
FROM WasteWeights
GROUP BY [streamID];

I get the 'streamid is no included in an aggregate function' error

[NeoPa]

If it doesn't work then it doesn't.

I don't have any live data to test on so just knocked something up. I wouldn't really expect it to work in Access (it may in SQL Server or other such full-featured SQL server).

I'm afraid your proposed solution will not return correct data even if it were to run. It's not asking the right question.

I think you need to return to the idea of multiple queries for this.

[charli]

Thank you for your help! I think I give up now, and I'll just settle on the overall average, rather than the average fo the last X records, its a bit too much for my poor brain to handle!

Thanks,
Charli

[NeoPa]

It's certainly not straightforward Charli.

The TOP Predicate is rarely ever usable outside of the main, outer query in my experience. Unless a specific grouping is selected of course. Where it would require running more than once to make sense (as in this case) it will always be hard if even possible.

[charli]

How I ended up doing this, incase anyone has the same problem:

Query 1 (called top10): SELECT TOP 10 streamid FROM table WHERE streamid=[Reports]![Confirm]![streamid] ORDER by [date] DESC;
-- returns the top 10 most recent records for an id

Query 2 (called Avgtop10): SELECT AVG(top10) FROM top10 WHERE streamid = [Reports]![Confirm]![streamid];
-- returns the average of the top 10 most recent records for an id

Then put Query Avgtop10 into a report (removing all the headers)
Made a second report (called Confirm) with a list of streamids (just SELECT streamid FROM table), embed the Avgtop10 report as a sub report.

This is a very long and convoluted way to get a list of IDs, and the average of the 10 most recent records for each id!

[NeoPa]

I'm not sure it is Charli.

It's convoluted yes, but then the requirement is convoluted. This seems to be a sensible solution to the problem. No more complex than you would reasonably expect for such a problem (in my view at least).

Congratulation for the solution by the way.