Forum Discussion
How to get a Weighted Average instead of an average of averages.
Hi, all. I am wanting to get a weighted average instead of an average of averages. See my excel example attached.
How would one go about getting a weighted average instead of an average of averages?
Thanks!
slohombre : Like this:
SELECT Week, Yield, Acres, [Yield]/[Acres] AS YieldPerAcre, (Select Sum(Acres) From Crop) AS TotalAcres, (Select Sum(Yield) From Crop) AS TotalWeight, [TotalWeight]/[TotalAcres] AS WeightedAverage FROM Crop;
Output:
Many thanks to both slohombre and GustavBrock for providing such clear explanations.
One point possibly worth mentioning.
It would be better to display the total weight(32) & total acres (19.5 not 20) as e.g Double numbers rather than integer.
The weighted average is correctly calculated as 32/19.5=1.641 rather than 32/20=1.6.
However, when doing aggregate queries, Access may not correctly calculate expressions based on other calculated expressions. Sometimes you will need to run the query twice for the correct results
I would therefore recommend either calculating the weighted average as a separate query or using the actual expressions for both Total Weight and Total Acres in the Weighted Average expression.
SELECT Week, Yield, Acres, [Yield]/[Acres] AS YieldPerAcre, Sum(Acres) AS TotalAcres, Sum(Yield) AS TotalWeight, Sum(Yield)/Sum(Acres) AS WeightedAv FROM Crop;