Distribution of names

Databases
1

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 

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

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

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

OK, I’m obviously a beginner… :confused:

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

Now you will need to goggle,
php pagination

Tons of examles there.