Forum Discussion

slohombre's avatar
slohombre
Brass Contributor
Mar 29, 2021

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:

    • isladogs's avatar
      isladogs
      MVP

      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;

Resources