Link to home
Create AccountLog in
Avatar of aphex4000
aphex4000

asked on

How do I correctly write a "Select Count (Distinct xx) AS" statement using VBScript in an ASP file?

I composed a "Select Count(Distinct xx) AS" statement in my ASP file using VBScript but I keep getting errors.  All my other SQL statements work just fine, but I have not tried to combine a COUNT with a DISTINCT until now.  I am not sure if what I am trying to do is even possible.

In the following code snippet I and trying to get a count of all unique items (unique TicketNr) in "tblRequest" where items in "tblRequestAssignee" have a specified Assignee and Status.  "tblRequest" and "tblRequestAssignee" share a key (TicketNr) hence the "tblRequest.TicketNr=tblRequestItems.TicketNr" in the code.

Is there a problem with the syntax in my code?  Any help would be greatly appreciated as it would be great if I could get this count with a single statement if possible.
SQLpendlm	= "SELECT COUNT (DISTINCT tblRequest.TicketNr) AS jCount FROM tblRequest, tblRequestItems WHERE tblRequest.TicketNr=tblRequestItems.TicketNr AND tblRequest.TicketAssignee = '" & xRecordset("TeamUserName") & "' AND tblRequestItems.LineMgrSupportStatus='Pending'"

Open in new window

Avatar of saoirse1916
saoirse1916
Flag of United States of America image

The first thing to try in this situation is to do a Response.Write(SQLpendlm).  That'll show you your actual SQL command as issued at runtime.  If it looks good, copy and paste it into Query Analyzer and run it to see if it's executing successfully.
Try this...
SELECT COUNT(DISTINCT tblRequest.TicketNr) AS jCount

Open in new window

Sorry, disregard my post. You already have the parenthesis and I just missed it for some reason.
ASKER CERTIFIED SOLUTION
Avatar of cb1393
cb1393
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of aphex4000
aphex4000

ASKER

Yes, I am using MS Access.  Sorry, I should have indicated that in my question.  I tested your query but it's still throwing an error "Syntax error (missing operator) in query expression"
cb1393, I got it working but I'm awarding you points as you pointed me a very specific and correct direction.  Thank you! :-)

Final Solution:

"SELECT COUNT (*) AS jCount FROM (SELECT DISTINCT tblRequest.TicketNr FROM tblRequest, tblRequestItems WHERE tblRequest.TicketNr=tblRequestItems.TicketNr AND tblRequest.TicketAssignee = '" & xRecordset("TeamUserName") & "' AND tblRequestItems.LineMgrSupportStatus='Pending')"
Given that the code is unfamiliar and has very little context, it was great that information was provided that let me to a working solution.  Thank you!

Working code:

"SELECT COUNT (*) AS jCount FROM (SELECT DISTINCT tblRequest.TicketNr FROM tblRequest, tblRequestItems WHERE tblRequest.TicketNr=tblRequestItems.TicketNr AND tblRequest.TicketAssignee = '" & xRecordset("TeamUserName") & "' AND tblRequestItems.LineMgrSupportStatus='Pending')"