How do I group by first name in MySQL?

Question

I have a table where there is a full name column. I want to get either the unique given name or given names sorted by how many times they occur.

fullname
-----------
Barack Hussein Obama
Michael Jackson
William Jefferson Blythe
Michael Bloomberg

so the output will be either

Barack Hussein Obama
William Jefferson Blythe

Or

Barack Hussein Obama
William Jefferson Blythe
Michael Jackson
Michael Bloomberg

Or

1|Barack
1|William
2|Michael

Something like that. My aim is to see the foreign students in my database. I only have their full name to make a guess.

@Vesper, there are full names with 2,3,4,5 "names". Like "raja muhammad omer faiz" or "muhammad salihudden bn mohamed bas" or "lucie helene marie sourice". — ilhan, May 14 '14 at 08:15
@ilhan Can you say which part is the "given name", then? Is it just the first name? Everything except the last name? — Carsten, May 14 '14 at 08:17
You can extract the first word, if you're sure that's the name you want, with a method like this: http://stackoverflow.com/questions/689858/mysql-query-to-extract-first-word-from-a-field and then handle the GROUP_BY separately. — Phssthpok, May 14 '14 at 08:19
Yeah, if you need the given name separately store it separately, it should be easy to do in whatever language is connecting to the db, but a pain if you are relying on MySQL for string functions... — serakfalcon, May 14 '14 at 08:19
Take look here http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x Perhaps this can help you — OliC, May 14 '14 at 08:23

score 5 · Accepted Answer · answered May 14 '14 at 08:21

You can use SUBSTRING_INDEX(fullname,' ',1) to extract the "given name" as per your definition.

You can then use this for grouping or sorting as you seem fit, e.g.

SELECT COUNT(*),SUBSTRING_INDEX(fullname,' ',1) AS givenname 
 FROM yourtable 
 GROUP BY givenname;

1 Answers1