1 Reply Latest reply on Oct 26, 2006 3:07 AM by score1

    Calculating percentage

    score1
      I am trying to write a query that calculates the average percentage score based on the number of modules an individual has sat, their total score being divided by the total max_score to give a percentage.

      The query I am using is:

      <cfquery name="passpercent">
      SELECT SUM(SCORE) AS TOTSCORE
      FROM (dbo.PPS_SCOS JOIN dbo.PPS_TRANSCRIPTS ON dbo.PPS_SCOS.SCO_ID = dbo.PPS_TRANSCRIPTS.SCO_ID)
      JOIN dbo.PPS_PRINCIPALS ON dbo.PPS_TRANSCRIPTS.PRINCIPAL_ID = dbo.PPS_PRINCIPALS.PRINCIPAL_ID
      WHERE dbo.PPS_PRINCIPALS.NAME LIKE '#start#'
      AND dbo.PPS_SCOS.NAME LIKE 'MT%'
      and PPS_TRANSCRIPTS.TICKET not like 'l-%'
      and pps_transcripts.max_score > 0
      and status like '[PF]'
      </cfquery>

      The #start# variable brings up the info for the selected individual out of an alphabetical list.

      There is a similar query for totalpercent.

      I am using the following toi calculate the average score:

      <cfset pass = passpercent.totscore>
      <cfset fail = totalpercent.maxscore>
      <cfset tot = pass + fail>
      <cfif tot GT 0>
      <cfset percent = pass / tot * 100>
      <cfelse>
      <cfset percent="0">
      </cfif>

      When trying to run I get the folowing error:

      The value "" cannot be converted to a number

      Any help appreciated.