By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,231 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Getting Weighted Average in Report

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

Expand|Select|Wrap|Line Numbers
  1. SELECT qRenPercReptSub.Merged, qRenPercReptSub.ccName, Sum(qRenPercReptSub.countofccname) AS SumOfcountofccname, Avg(qRenPercReptSub.AvgOfcpUpcomingRen) AS AvgofRen, Sum(qRenPercReptSub.SumOfcpUpcomingRen) AS TotalofRen
  2. FROM qRenPercReptSub
  3. GROUP BY qRenPercReptSub.Merged, qRenPercReptSub.ccName;
  4.  
Here is some sample data:

Merged ccName SumOfcountofccname AvgofRen TotalofRen
Atlanta Aetna 8 0.2092 1.6736
Atlanta BCBS of GA 11 0.152090909090909 1.673
Atlanta Coventry 5 0.1712 0.856
Atlanta UHC 3 0.113333333333333 0.34

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

Thanks!

LSGKelly
Feb 15 '10 #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,597
Are you assigning various Percentages to each Value of AvgofRen to represents the 'Weights'?
Feb 15 '10 #2

nico5038
Expert 2.5K+
P: 3,072
Does this work for you:
Expand|Select|Wrap|Line Numbers
  1. SELECT qRenPercReptSub.Merged, qRenPercReptSub.ccName, Sum(qRenPercReptSub.countofccname) AS SumOfcountofccname, Avg(qRenPercReptSub.AvgOfcpUpcomingRen) AS AvgofRen, Sum(qRenPercReptSub.SumOfcpUpcomingRen) AS TotalofRen
  2. FROM qRenPercReptSub, Sum(qRenPercReptSub.SumOfcpUpcomingRen) / DSum("SumOfcpUpcomingRen","qRenPercReptSub") AS WeightedTotalofRen
  3. FROM qRenPercReptSub
  4. GROUP BY qRenPercReptSub.Merged, qRenPercReptSub.ccName;
  5.  
It will be slow because of the DSUM(), so be warned....

Nic;o)
Feb 18 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.