Getting Weighted Average in Report

[LSGKelly]

I am trying to get the weighted average into my report. I have a query:

SELECT qRenPercReptSub.Merged, qRenPercReptSub.ccName, Sum(qRenPercReptSub.countofccname) AS SumOfcountofccname, Avg(qRenPercReptSub.AvgOfcpUpcomingRen) AS AvgofRen, Sum(qRenPercReptSub.SumOfcpUpcomingRen) AS TotalofRen
FROM qRenPercReptSub
GROUP BY qRenPercReptSub.Merged, qRenPercReptSub.ccName;

Here is some sample data:

Merged ccName SumOfcountofccn ame AvgofRen TotalofRen
Atlanta Aetna 8 0.2092 1.6736
Atlanta BCBS of GA 11 0.1520909090909 09 1.673
Atlanta Coventry 5 0.1712 0.856
Atlanta UHC 3 0.1133333333333 33 0.34

I need to get the weighted average of AvgofRen. Can I do this in a report?

Thanks!

LSGKelly

[ADezii]

Are you assigning various Percentages to each Value of AvgofRen to represents the 'Weights'?

[nico5038]

Does this work for you:

SELECT qRenPercReptSub.Merged, qRenPercReptSub.ccName, Sum(qRenPercReptSub.countofccname) AS SumOfcountofccname, Avg(qRenPercReptSub.AvgOfcpUpcomingRen) AS AvgofRen, Sum(qRenPercReptSub.SumOfcpUpcomingRen) AS TotalofRen
FROM qRenPercReptSub, Sum(qRenPercReptSub.SumOfcpUpcomingRen) / DSum("SumOfcpUpcomingRen","qRenPercReptSub") AS WeightedTotalofRen
FROM qRenPercReptSub
GROUP BY qRenPercReptSub.Merged, qRenPercReptSub.ccName;

It will be slow because of the DSUM(), so be warned....

Nic;o)