Simplfied SQL Statment

Hi all

This is my first post on here, even tho i've been using this forum for a very long time and usually i do find what i am looking for except this time.

I am trying to count how many users i have in my database start with the letter 'a' and 'b' and so on all the way to 'z'.

Now i have written some code but i am sure there is an easier way of doing this. Here is my code.
Set conn = Server.CreateObject("ADODB.Connection")
conn.open connStr

strSQLa = "SELECT Cust_Username AS acount FROM CustRecords WHERE Cust_Username LIKE 'a%'"
Set rs = conn.Execute(strSQLa)
do until rs.EOF
acount = acount + 1
rs.MoveNext
Loop

strSQLb = "SELECT Cust_Username AS bcount FROM CustRecords WHERE Cust_Username LIKE 'b%'"
Set rs = conn.Execute(strSQLb)
do until rs.EOF
bcount = bcount + 1
rs.MoveNext
Loop
rs.Close
 
Now as you can see to do this all the way to 'z' would be alot of code for some thing that i feel can be done is a few line.
The total amount of users starting with the letter 'a' and 'b' do need to be in a seperate string.

Hope someone can help

Rick

There are a couple of ways to do this. Personally I would simply get all the names back then count each starting letter in vbscript code.

Thanks for the reply
 
An example would be nice!!!
 
 

As can be seen from this demo script:

 ... deleted because of problems when trying to post the full message ...
 

resp. its output:

 cscript groupsql.vbs
 SELECT  LastName FROM Customer
 A1whatever
 A2whatever
 B1whatever
 B2whatever
 B3whatever
 C1whatever
 C2whatever
 C3whatever
 C4whatever
 
 SELECT SUBSTR( LastName, 1, 1 ), COUNT( LastName ) FROM Customer GROUP BY SUBSTR
 ( LastName, 1, 1 )
 A       2
 B       3
 C       4
 

the important message is: Use "GROUP BY"!