We help IT Professionals succeed at work.

How to display Percentage instead of Record Count?

baxtalo
baxtalo used Ask the Experts™
on

Instead of the number of occurances where the camera was not working in the store I'd like to display the percentage. Would anyone please help me with this?
I need the percentage not the record count.
Thank you

<%
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("StoreWatch.mdb") & ";"
objConn.Open

Set sqlCount = Server.CreateObject("ADODB.Command")
sqlCount.ActiveConnection=objConn
sqlCount.Prepared = true
sqlCount.CommandText = "SELECT Count(id) AS Countid FROM StoreWatch_Table where Camera = 'No'"
set objRs = sqlCount.Execute
CountID = int(objRs("CountID"))
%>
<%=Countid%>
<%
ObjRs.Close
Set ObjRs = Nothing
%>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
sqlCount.CommandText = "SELECT 100.0*Count(case when Camera = 'No' then 1 else 0 end)/Count(*) AS PercentageNo FROM StoreWatch_Table"
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
To be more exact
<%
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("StoreWatch.mdb") & ";"
objConn.Open

Set sqlCount = Server.CreateObject("ADODB.Command")
sqlCount.ActiveConnection=objConn
sqlCount.Prepared = true
sqlCount.CommandText = "SELECT 100.0*Count(case when Camera = 'No' then 1 else 0 end)/Count(*) AS PercentageNo FROM StoreWatch_Table"
set objRs = sqlCount.Execute
CountID = CDbl(objRs("PercentageNo"))
%>
<%=Countid%>
<%
ObjRs.Close
Set ObjRs = Nothing
%>

Open in new window

Author

Commented:
I'm sure we are very close but I get an error message on the line where the sql is: '80004005'
I'm playing with it but no success yet. Would you know what it is? It won't give me more details, just the number above.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Try this:
<%
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("StoreWatch.mdb") & ";"
objConn.Open

Set sqlCount = Server.CreateObject("ADODB.Command")
sqlCount.ActiveConnection=objConn
sqlCount.Prepared = true
sqlCount.CommandText = "SELECT 100.0*Count(case when Camera = 'No' then 1 else 0 end)/Count(*) AS PercentageNo FROM StoreWatch_Table"
set objRs = sqlCount.Execute
%>
<%=""&objRs("PercentageNo")%>
<%
ObjRs.Close
Set ObjRs = Nothing
%>

Open in new window

Author

Commented:
In my column I have Yes and No options. Users submit it by a radio button. That's why I'm using " FROM StoreWatch_Table where Camera = 'No'"
Doesn't the error has to do something with the 'when' instead of 'where'?
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
I hadn't realised this is MS Access not SQL Server.
My apologies.
<%
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("StoreWatch.mdb") & ";"
objConn.Open

Set sqlCount = Server.CreateObject("ADODB.Command")
sqlCount.ActiveConnection=objConn
sqlCount.Prepared = true
sqlCount.CommandText = "SELECT 100.0*Sum(IIF(Camera = 'No',1,0))/Count(*) AS PercentageNo FROM StoreWatch_Table"
set objRs = sqlCount.Execute
%>
<%=""&objRs("PercentageNo")%>
<%
ObjRs.Close
Set ObjRs = Nothing
%>

Open in new window

Author

Commented:
It's my foult, I should have mentioned it's MS Access. Now it's working, It just gives me too many numbers:
25.4716981132075
How could I make it shorter?

Author

Commented:
I just need it like this: 25%
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
>>It's my foult, I should have mentioned it's MS Access.

No need to apologize.  I read more and saw Jet and .MDB and it clicked.
Give this a go


sqlCount.CommandText = "SELECT Round(100.0*Sum(IIF(Camera = 'No',1,0))/Count(*) AS PercentageNo FROM StoreWatch_Table"
set objRs = sqlCount.Execute
%>
<%= "" & Fix(CDbl(objRs("PercentageNo"))+0.5) & "%" %>

Author

Commented:
Thank you very much for your patience with me, I don't have too much experience. This is perfect now. Beautiful....

Author

Commented:
It gives me an error, but this is a different question anyway. I will post it again another time. Thank you so much.

Syntax error (missing operator) in query expression 'Round(100.0*Sum(IIF(Camera = 'No',1,0))/Count(*) AS PercentageNo FROM
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
I was going on 2 approaches at once - both interfering... sincere apologies again

Revert the sql:

sqlCount.CommandText = "SELECT 100.0*Sum(IIF(Camera = 'No',1,0))/Count(*) AS PercentageNo FROM StoreWatch_Table"

Author

Commented:
I was playing with it and I adde a )
Now it's working like this:

SELECT Round(100.0*Sum(IIF(Camera = 'No',1,0)))/Count(*) AS PercentageNo FROM