Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005
    Posts
    2

    Unanswered: Tricky query - listing alphabetically with freqency

    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 showing a number representing how many members last name (Lname) starts with that particular letter.

    So far ive got:

    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
    Last edited by GolfingTea; 02-23-05 at 22:45.

  2. #2
    Join Date
    Jul 2004
    Posts
    102
    Try this

    select substr(ename,1,1), count(*) from emp
    group by substr(ename,1,1);

    S COUNT(*)
    - ----------
    A 2
    B 1
    C 1
    F 1
    J 2
    K 1
    M 2
    S 2
    T 1
    W 1
    Working Together...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •