asked on

SQL query to return percentage

Hi Experts,
The script below returns the number of a certain product from my Products table.
I would like to display the percentage instead. How can this script be modified in order to achieve that?
Thank you for your help.

<%
Set sqlCount = Server.CreateObject("ADODB.Command")
sqlCount.ActiveConnection=Conn
sqlCount.Prepared = true
sqlCount.CommandText = "SELECT Count(ID) AS Countid FROM Products where myProduct = 'Beer'"
set objRs = sqlCount.Execute
CountID = int(objRs("CountID"))
%>
<%=CountID%>
<%
ObjRs.Close
Set ObjRs = Nothing
%>

Open in new window

Jim Horn

You'll want to spell out 'percentage'. If you mean the count of 'Beer' over the count of everything else, replace the SELECT statement in line 5 with..

SELECT COUNT(id) / CAST((SELECT COUNT(*) FROM Products) as numeric(19,4)) as avg
FROM Products
WHERE myProduct = 'Beer'

Open in new window

Big Monty

try:

SELECT ( count( ( id ) * 100 / ( SELECT count( * ) FROM Products ) ) as avg
FROM Products
WHERE myProduct = 'Beer'

Stefan Motz

ASKER

The_Big_Daddy: I get an error message saying "Incorrect syntax near the keyword 'as'."

JimHorn: The error message for line 7 is "Item cannot be found in the collection corresponding to the requested name or ordinal."

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Big Monty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stefan Motz

ASKER

I've changed line 7 and now the error message is the following: "Variable uses an Automation type not supported in VBScript"
I'm using classic ASP, maybe that's the reason

Stefan Motz

ASKER

Thank you very much both of you; it worked this time!