Average of 16 best measurements 1

[flaviooooo]

Hey,

I have a table which contains all measurements per material.

Now I would like some kind of statistic, which would show me the average of the 5 highest measurements, per material.

So for example Material X has the following measurements:
12
15
4
30
11
55

The 5 highest are 55,30,15,12,11 -> average is 24,6


The table has 2 fields, simply "Material" "Measurement"

I don't know if the following addition is also possible: if a material has only 4 measurements, the sum should only be divided by 4...

Any help would be greatly appreciated

 

[PHV]

What about this ?
SELECT T.Material, Avg(T.Measurement) AS Average
FROM (SELECT A.Material, A.Measurement FROM yourTable AS A
INNER JOIN yourTable AS B ON A.Material = B.Material AND A.Measurement <= B.Measurement
GROUP BY A.Material, A.Measurement HAVING Count(*) <= 5) AS T
GROUP BY T.Material

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[flaviooooo]

Perfectomundo

Thanks and have a star