Averaging the total score for a multi-question evaluation...

I have a evaluation form that collects a score (1 - 10) and some comments.
Several people will evaluate one person, in turn submitting a form for each evaluation.
I need to average the scores for each question from all the Evaluators and
display the total average and each comment from all the evaluators in one result record
for each question.

EG:

Eval1            Eval2           Eval3          Eval4
5                  4                 7               6
Comment1    Comment2   Comment3  Comment4


Results
Overall Eval
5.5
Comment1
Comment2
Comment3
Comment4
jollymon6672Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mkishlineCommented:
<cfquery name="queryname" datasource="dbname">
  SELECT grade, comment, person
  FROM tablename
  WHERE person = "criteria"
</cfquery>

<cfoutput query="queryname" group="person">
      #ArrayAvg(ListToArray(ValueList(queryname.grade)))#
      <cfoutput group="headline">
      #comment#
      </cfoutput>
</cfoutput>
0
JeffHowdenCommented:
Alternatively, make your database engine do what it's good at (the math)

<cfquery name="queryname" datasource="dsn">
  SELECT (eval1 + eval2 + eval3 + eval4) / 4 AS avg_grade
            , comment
            , person
    FROM tablename
</cfquery>

This will work if every record has an evaluation.  However, if some records only have none, 1, 2, or 3 evaluations, that'll throw the entire thing off and will require a much more complicated solution than either I or mkishline has suggested.
0
jollymon6672Author Commented:
Does this measn I need a query for each question in the eval or can I loop through the eval from all submitters and generate the total evaluation?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

mkishlineCommented:
Can you describe how your database is set up? That may help abet some of the confusion.
0
JeffHowdenCommented:
Also, please indicate whether or not every question will have a non-NULL/non-zero value for eval1, eval2, eval3, and eval4.
0
digicidalCommented:
Assuming that your evaluation field is an INT value and is required, then no you would simply be able to loop through the query as indicated in mkishline's example.  Using ArrayAvg() in his example will work just fine, or if you would like to have a long-hand version you could use the following:

<cfquery name="queryname" datasource="dbname">
  SELECT grade, comment, person
  FROM tablename
  WHERE person = "criteria"
</cfquery>
<cfset GradeTotals = 0>
<cfoutput query="queryname">
Eval#queryname.CurrentRow# #grade# #comment#<br>
<cfset GradeTotals = GradeTotals + grade>
</cfoutput>
<cfoutput>
Results (Overall Average): #Evaluate(GradeTotals/queryname.RecordCount)#
</cfoutput>
0
JeffHowdenCommented:
The database would be the more appropriate place to do the mathematical calculations as it's much faster at them than CF.

Also, it'd be more appropriate in the following snippet "#Evaluate(GradeTotals/queryname.RecordCount)#" to use the Val() function instead of the Evaluate() function:

#Val(GradeTotals / queryname.RecordCount)#

You should only ever use the Evaluate() function in extremely rare circumstances.
0
digicidalCommented:
Really?  According to my CF manuals the Val() function is for converting numeric characters at the beginning of a string into a number, while Evaluate() is stated indicated for evaluating expression(s).  I never knew that you could use Val() in that way - and Macromedia doesn't indicate that you can either.

Like most things in CF... it figures out what you wanted (most of the time accurately)... consider the following:

<cfscript>
y = 200;
z = 3;
</cfscript>
<cfoutput>VAL: #Val(y/z)# | EVALUATE: #Evaluate(y/z)# | NONE: #y/z# | FORMAT: #NumberFormat(y/z,'999.999')#</cfoutput>

With the exception of the last item (which because of the mask is rounded to only 3 decimal places - they all return the same thing (if you replaced the mask with '999.9999999999' then they would all be exactly the same.

I have to disagree with Jeff about using Val as in many discussions I've had with other developers the same could be said about it...  "You should only ever use the Val() function in extremely rare circumstances."  However, he is right in Evaluate - being a function - having drawbacks.  If the absolutely tighest code possible is desired... then use #GradeTotals/queryname.RecordCount# with no function call whatsoever.  It evaluates just fine without one - or just <cfset variable = GradeTotals/queryname.RecordCount> and then output #variable#.  Many possible solutions and all work.

Cheers.
0
JeffHowdenCommented:
Digicial, I realize you are just going off the description for the Val() function from Macromedia's ColdFusion language reference.  That's fine to do, however, it  is considered best practice by developers considered to be experts in the CF community to use the Val() function to ensure the data you're working with is numeric.  Additionally, most, if not all, of these same expert developers will strongly discourage the use of the Evaluate() function except when all other options have been exhausted.

To the OP though, for the very tightest code possible, make sure to enforce a number for GradeTotals and a non-zero recordcount lest errors be thrown in circumstances where GradeTotals isn't a number or recordcount results in a divide by zero error:

<cfscript>
  avg = 0;
  if(queryname.recordcount)
    avg = Val(GradeTotals) / queryname.recordcount;
</cfscript>
<cfoutput>#avg#</cfoutput>

Be *very* careful of using #somevar + someothervar# as it may give you unintended results.  For example, if you end up having to put the code on a pre-CF5 box, that particular snippet of code will throw errors.  Granted, there aren't many pre-CF5 boxes in production, but better safe than sorry.  Additionally, if you don't ensure that "somevar" and "someothervar" are both numeric, attempting an arithmetic operation will result in an error.

0
digicidalCommented:
Well, I wholeheartedly agree with you as far as Evaluate() not being the best solution - I was in error using it (it's an old habit unfortunately from when I just moved to CF from working in a couple other languages where it would have been used in that way).  And I do agree with you on Val() being better as far as using a function to return a number.  I've however spoken to several experts in CF (two of whom worked on MX - writing it... not developing in it) and they indicated several reasons that it would not optimize as well in the compiler due to the way the string conversion functions in Java operate.  I have to take their word on this, however I have run into problems with it myself due to it not being able to handle commas or exponential notation for numbers (which granted, are not that common, unless say you're using Excel as a COM object).

In any case I completely agree with your solution.  I would even adjust the check for RecordCount to occur prior to any of the output, so that if queryname.recordcount EQ 0 then the entire output is skipped and a more user friendly message is displayed.  

This would be fastest to process anyway.  Also according to a discussion with a friend that is a big J2EE (pure java) programmer - who doesn't know CF but I showed him the function options, recommended that in his opinion (again based on how he'd write it in java) the best option would probably be to use "IsNumeric()" to check for a number and based on that boolean value compute or escape the calculation function.  Again you're way works great - just some more food for thought.

For this question I guess that would give us (assuming that you didn't already check for recordcount elsewhere:
<cfscript>
  avg = 0;
  if(queryname.recordcount AND IsNumeric(GradeTotals))
    avg = GradeTotals / queryname.recordcount;
</cfscript>
<cfoutput>#avg#</cfoutput>
0
jollymon6672Author Commented:
WOW, I never thought my question would produce such an indepth conversation on best practice. It is great to know that there are so many "Experts" out there.

To answer so previous question:

My DB table (Access 2003) is as follows:

There are 18 questions total, several people will be evaluating a individual, each one has:

1. A numeric value of 1 -10
2. A Comment field

As mentioned I am trying to query the table, return the values and the average score per question, while also returning all comments for each question from all evaluators.

So the results should be:

Question 1: How well does this player communicate
Average score: 7.0
Comments:
Eval1: Does a good job
Eval2: What a talker
Eval3: Never shuts up
Eval4: Learn english

It is not a high traffic site so I only need Access for the DB.
0
digicidalCommented:
Here is one solution that will work I think... give it a try (after changing the names in the queries to the actual fields, datasource name, and the personvalue variable of course!  Note: You should be able to do this with an inline calculation and grouping on the query itself, but I'm not sure of Access syntax for this... so here is one solution using a query of queries function.  I'm sure I've made a mistake here since I've been up for way too many hours... but give this a shot.

<cfquery name="questionQuery" datasource="myDatasource">
SELECT QuestionNumber, QuestionText FROM questiontable ORDER BY QuestionNumber ASC
</cfquery>
<cfquery name="scoreQuery" datasource="myDatasource">
SELECT QuestionNumber, Score, Comment FROM scorestable WHERE PersonReviewed = <cfqueryparam value="#personvalue#">
</cfquery>
<cfloop query="questionQuery">
    <cfquery name="tempQuery" dbtype="query">
      SELECT * FROM scoreQuery WHERE QuestionNumber=#questionQuery.QuestionNumber#
    </cfquery>
      <!--- Now we have all the query results from our question so we'll pre-process --->
      <cfscript>
      procError = FALSE; // In case we get errors so we can 'crash' softly.
      if (tempQry.Recordcount) {
            // First we'll zero out our variables.
          scoreTotal = 0;
            avgScore = 0;
            // Now loop over our temp query and calculate our total.
            For (i=1;i LTE tempQry.Recordcount; i=i+1) {
                  tempScoreTotal = tempScoreTotal + val(tempQry.Score[i]);
            }
            // Now calculate average from our culled values for this question.
            avgScore = scoreTotal/tempQry.Recordcount;
            } else {
            // ERROR - Prevents Divide By Zero
            procError = TRUE;
      }
      </cfscript>
      <cfif procError>
            <b>No scores were found for Question #questionQuery.QuestionNumber#</b><br /><br />
            <cfelse>
                  <cfoutput>
                  <hr />
                  Question #questionQuery.QuestionNumber#: #questionQuery.QuestionText#<br />
                  Average Score: #NumberFormat(avgScore,"9999.999")#<br />
                  Comments:<br />
                        <cfloop query="tempQuery">
                        Eval#tempQuery.CurrentRow#: #tempQuery.Comment#<br />
                        </cfloop>
                  <hr />
                  </cfoutput>
      </cfif>
<cfloop>

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JeffHowdenCommented:
Since you need to retrieve the comments from each evaluator, you really won't be able to query the average at the DB level.  So, that leaves constructing the query for the entire resultset and then some logic for determining the average score.  I realize that each score has a value from 1-10, however, the extra logic will allow you to permit some questions to be evaluated by less than 4 evaluators and the average score will still be accurate.

<cfquery name="queryname" datasource="dbname">
  SELECT question, eval1, comment1, eval2, comment2, eval3, comment3, eval4, comment4
    FROM tablename
  WHERE person = "criteria"
</cfquery>

<cfloop query="queryname">
  <cfscript>
    evaluators = 0;
    total_score = 0;
    avg_score = 0;
    for(i = 1; i LTE 4; i = i + 1)
    {
      if(Len(Trim(queryname['eval' + i][currentrow])))
      {
        evaluators = evaluators + 1;
        total_score = total_score + Val(queryname['eval' + i][currentrow]);
      }
    }
    avg_score = total_score / evaluators;
  </cfscript>
  <cfoutput>
    Question #currentrow#: #question#<br />
    Average Score: #DecimalFormat(avg_score)#<br />
    Comments:<br />
    Eval1: #comment1#<br />
    Eval2: #comment2#<br />
    Eval3: #comment3#<br />
    Eval4: #comment4#<br />
    <br />
  </cfoutput>
</cfloop>
0
jollymon6672Author Commented:
Jeff,

What you appear to be proposing suggests that the questions are also stored in the DB table. So, I would have to loop over them for the initial rendering of the evaluation form. Correct?
0
JeffHowdenCommented:
You mean for those scoring the questions?
0
jollymon6672Author Commented:
Currently my table only holds the Evalualtor ID, The question rating and the comments.
0
JeffHowdenCommented:
Where are the questions stored then?  How do you know what question a record in this table is for?
0
jollymon6672Author Commented:
The questions were coded in the HTML. Sounds like I need to store them in the DB. Suggestions on the best method to organize the table?
0
JeffHowdenCommented:
I think that goes beyond the scope of your original question.  Probably best to close this one out, marking the expert's response that helped you the most, and opening a new question about database structure with a link to this question for background.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.