Producing a list from A-Z, showing amount of members with that initial

Hi everyone,

I know this is a very tricky question but any recomended approaches would be much appretiated

Im using Oracle8i / SQL and have a table of members (table: Members), the column I'm working with here is Lname...

Im trying to find how to develop a query that lists for every letter of the alphabet how many members' last names start with this letter.

So the list will have 2 columns whereby the 1st contains letters of the alphabet, with the 2nd containing numbers representing how many members last name (Lname) starts with that particular letter.

So far I have:

select SUBSTR(Lname,0,1), count(*)
from Member group by Lname;

but this is way to simple and returns:

S Count
- ------
c 1
c 1
c 1
p 1
f 1
f 1

Anyone have any ideas?

Thanks in advance

Just Change the Query as given below and you should have the answer.

select SUBSTR(Lname,1,1), count(*)
from Member group by SUBSTR(Lname,1,1);