Stefan Motz
asked on
SQL Calculate Percentage
Hi Experts,
I'm trying to calculate the percentage of complaint and compliment letters from the total number of letters received. The query below doesn't seem to calculate it correctly. (e.g. instead of 33.33% is shows 3.03%)
How can this query be modified so that it displays the correct percentage?
Thank you for your help
I'm trying to calculate the percentage of complaint and compliment letters from the total number of letters received. The query below doesn't seem to calculate it correctly. (e.g. instead of 33.33% is shows 3.03%)
How can this query be modified so that it displays the correct percentage?
Thank you for your help
<%
Set sqlCount = Server.CreateObject("ADODB.Command")
sqlCount.ActiveConnection=Conn
sqlCount.Prepared = true
sqlCount.CommandText = "SELECT cast(cast(COUNT(id) as decimal)*100 / cast((SELECT COUNT(*) FROM HR_Letters)as decimal )as decimal(5,2)) as avg FROM HR_Letters WHERE Letter='Compliment'"
set objRs = sqlCount.Execute
CountID = objRs("avg")
%>
<%=CountID%>
<%
ObjRs.Close
Set ObjRs = Nothing
%>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help. Both solutions are correct.
It just skipped my attention that I have to add and extra condition in my customized query.
It just skipped my attention that I have to add and extra condition in my customized query.
ASKER