troubleshooting Question

Average of numbers - count only records with complete data

Avatar of JLohman
JLohman asked on
DatabasesASPMicrosoft SQL Server 2008
2 Comments1 Solution264 ViewsLast Modified:
I have a DB with a table that contains the core data (name, dept, RateJournal, RateBooks....). I have code written which will average the data for each field. The problem is some of the records do not have data for the "Rate.." fields. I want to count and average only those records for fields which have a number greater than 0. Not sure how to delete the incomplete records. Following is working code, but it is counting all records and then averaging. I am new to this type of calculations, please advise. Thanks.

'Number of records with priority rankings
sqlCount = "SELECT count(*) AS records FROM tblClinicians "
Set objCount = objConn.Execute(sqlCount)
sqlRank = "SELECT ROUND(AVG(tblClinicians.RateJournal), 2) AS RateJournal, " & _
                                                "ROUND(AVG(tblClinicians.RateBooks), 2) AS RateBooks, " & _
                                                "ROUND(AVG(tblClinicians.RateDatabase), 2) AS RateDatabase, " & _
                                                "ROUND(AVG(tblClinicians.RatePOC), 2) AS RatePOC, " & _
                                                "ROUND(AVG(tblClinicians.RateMultiple), 2) AS RateMultiple, " & _
                                                "ROUND(AVG(tblClinicians.RateOther), 2) AS RateOther " & _
            "FROM tblClinicians "
Set objRank = objConn.Execute(sqlRank)
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta http-equiv="Subject" content="Library Orientation Evaluation">
<meta http-equiv="Keywords" content="Data Collection, Department of Veterans Affairs, Evaluation Studies, Inservice Training, Libraries, Library Orientation, Library Services, Organization and Administration, Personnel Management, Questionnaires, VA Library Network, VALNET">
<h2 align="left"><font face="Arial" size="4"><%=title%></font></h2>
<p><b><font size="2" face="Arial">Total number of evaluations completed (includes ONLY those responses where rankings were completed):
Ranking: importance of the type of online resources (#1 is first/top priority -
#2 is second/2nd priority)
  <li>Journals:  <%=objRank("RateJournal")%> </li>
  <li>Books:  <%=objRank("RateBooks")%> </li>
  <li>Database:  <%=objRank("RateDatabase")%> </li>
  <li>Point of Care Products:  <%=objRank("RatePOC")%> </li>
  <li>Multiple Resources in One:   <%=objRank("RateMultiple")%> </li>
  <li>Other:  <%=objRank("RateOther")%> </li>
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros