Calculating percentage

[Guest]

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.

[Guest · Accepted solution]

Managed to fix this by using the following:

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