SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Oct 2004
    Location
    Italy
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Distribution of names

    Hello all,

    I have membership table ('members') and, regarding the surnames (in field 'surname'), I would like to generate the following:
    number of members whose surname starts with 'A': xxx
    number of members whose surname starts with 'B': yyy
    etc, for all the letters of the alphabet.

    Is there a way of achieving this in ONE query, or do I really have to loop through all 26 letters, with one query per letter?...

    Many thanks for your feedback.

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT COUNT(*) cnt
         , LEFT(surname, 1) letter
      FROM members
    GROUP
        BY letter
    ORDER
        BY letter
    This will do more than just letters though. If surnames exist where the first character isn't a letter, you'll need to tell us what you want to happen(ignore them?).

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2004
    Location
    Italy
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, no... these are not two tables: just ONE table ('members') and I don't care about any other field at the moment, just the surname FIELD ('surname'). And I need to count the number of members whose surnames start with each of the letters of the alphabet...

    Thanks.

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Use the query crmalibu provided for the count per letter. Then fill in the missing gaps on the application layer. The only other solution will be to create a separate table containing all characters of the alphabet and joining on it… which is a bit of a overkill considering you can just do it on the application layer. The only problem will be if the alphabet ever changes…

    However, considering that is highly unlikely I think your alright with filling in the gaps on the application layer.

  5. #5
    SitePoint Zealot
    Join Date
    Oct 2004
    Location
    Italy
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I'm obviously a beginner...

    The snippet provided by crmalibu works perfectly -- I had initially "complained" (my apologies!) because I don't understand how it works... It's beyond my MySQL abilities...

    And since we're here and I've eaten my slice of humble pie, I have another question that's related.

    There are some letters that have too many entries in them, so I would like to display the names in two separate pages. Let's say that letter 'D' has too many entries; I would like to display things in two pages: the first with all the entries (in alphabetical order) from the first 'D' until the entry before the first entry that starts with 'DI...', while the second page starts with 'DI...' to the last entry in the Ds -- was that clear? What is the simplest way of determining where the break point is located?

    Thanks again for the help.

  6. #6
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,094
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Now you will need to goggle,
    php pagination

    Tons of examles there.
    What I lack in acuracy I make up for in misteaks


Bookmarks

Posting Permissions

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