SQL Record Count Question

[theflyingminst]

Hi I am using ASP with Access and I wanted to know if someone could give me their opinion on the best way to get a specified recordcount.

Basically, I want to query a column to see how many unique values there are and then put the count next to it in parentheses. Look at the code below to reference.


Set oRs=Server.CreateObject("adodb.recordset")
strSQL = "SELECT DISTINCT Column FROM Table ORDER BY Column"
oRs.Open strSQL, conn,3,3
count=oRs.recordcount

Do while not oRs.EOF


Response.Write "<OPTION VALUE = '" & oRs ("Column") & "'>"
Response.Write oRs("Column") & " (" & count & ")</Option>"
oRs.MoveNext
loop


Thank you

[Charles]

This might be more of an ASP question than an SQL question and I don't know squat about ASP. But I have recently found myself using ADODB Recordsets quite a lot but with JScript.

If ASP is case sensitive I see a few problems there. But I'm confused about what you are trying to accomplish. RecordSet.RecordCount will give you the total number of records returned by the query but What is it that you want it to give you? Do you want the number of entries for each unique value? Then "SELECT Column, COUNT(*) as [Num] FROM Table GROUP BY Column" is your best bet.

[theflyingminst]

Thanks Charles, yeah basically I need to get the count of each unique value in the column and output it accordingly in the proper drop down box.

That code looks sweet, I'm gonna give it a whirl.