Weighted Average in SQL

[Ashe]

Hello,
I'm trying to create a function in an access query that will create a weighted average. For groups of projects, there is a status and a related rank. For each group the basic idea would be:
(project status)*(project rank)/(sum(project rank)

I've tried the following sql code in query design, but I'm getting an aggregation error

WA: (([tbl_project].[status]*[tbl_targets].[RANK])/(Sum([tbl_targets].[RANK])))

I had tried the code below, just an average of the projects and their ranks, but this isn't truly a weighted average, and it's certainly showing in my results.

WA: Avg([tbl_project.[status]*[tbl_targets].[RANK])

Any suggestions of guidance would be greatly appreciated. Thank you!

[Ashe]

I should note the exact error is "You tried to execute a function that does not include the specified function '[tbl_project].[status]*[tbl_targets].[RANK])/(Sum([tbl_targets].[RANK]' as part of an aggregate functions.

I'm hoping that this is because I'm not dividing correcting or something like that...

[June7]

In the Total row of the query select Expression.

[Ashe]

That's set in the query. Still getting the error... (thanks though!)

[Ashe]

Update:

I found some threads online that indicate that using division in a query causes an error because it is not a function the query immediately recognizes (as it is not an option from the drop-down list in the "Totals" row). By typing the expression and setting the "Totals" row to "Group By" I was able to forgo the aggregation run-time error.

I think there's still some explanation needed as to why division isn't an automatic option for Access queries, but since the queries working, I can't complain too much.

[June7]

I never encountered problem with division in query expressions. Division is an operation just like +, -, *, not a function. You would get the same error message with any of the operators. Apparently the issue is with the divisor because it is not an aggregate calc. Guess I never tried one like that before.

[Ashe]

You're right. I thought I had it figured out, but I had to average everything I wanted to divide with to make it an aggregate. I'm still not confident that I'm getting a weighted average at this point.

[June7]

Here is a sample calc from my project: Sum([AdjPCI]*[Area])/Sum([Area]). Not my formula, I was told to use it and they call it a weighted average. It fits the example of this article http://www.meadinkent.co.uk/xlwtdavg.htm

Check out Wiki's info http://en.wikipedia.org/wiki/Weighted_mean
and this http://wiki.answers.com/Q/What_is_a_weighted_average

[Ashe]

Yup- this is what I have too. Except for some reason, now it seems I need to squeeze more IIf qualifiers in.
I have occassional blank(null) instances that are being calculated into the weighted average.
I must not be applying these correctl- (as Access is rejecting them).
I tried putting them in after the sum functions, but this didn't work. ( for example: /(sum(IIf(tbl_rank.rank)))). I also tried umbrellaing the IIf before the whole expression, but I guess this isn't the correct syntax either (judging by the error in expression message). Any suggestions?


Thanks for all your help!

[June7]

Your IIf expression is incomplete.

Here is example with IIF:
Sum(IIf([Use]="Taxiway",[AdjPCI]*[Area],0))/Sum(IIf([Use]="Taxiway",[Area],0))

I don't actually have data here now to check this formula for Null issue, not sure my data has any Nulls.

Sum aggregate calc should ignore Nulls. Test that by doing a simple Sum on a field with Nulls.