CODEWITHSUNDEEP
phpmysqlregex
unicornication
unicornication

Reputation: 627

Counting and grouping similar strings in a mysql table?

I have a database table, users, with a lot of names and genders in it, and I need to find the most common first name for either gender?

|      name      |   sex  |
----------------------------
|     Foo Bar    |  male  |
|     Foo Boq    |  male  |
|     Plo Boa    |  male  |
|     Loo Baa    | female |
|     Boo Faa    | female |
|     Boo Sar    | female |

The best I can come up with is something along the lines of (for e.g. males)

SELECT  name, COUNT(*) totalCount
FROM    users WHERE sex='male'
GROUP   BY name
ORDER   BY totalCount DESC
LIMIT   1

The result I was hoping for was 

Foo

After which I realized that doesn't take last names into account. There's too much data in this table for me to try and separate the data (unless there's relatively easy way I can do that?)

Would it be possible to use REGEX or some permutation of '$name%' (Running the query with PHP!)

Thanks! :-)

Upvotes: 1

Views: 67

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can use SUBSTRING_INDEX(name,' ',1) in GROUP BY,As first name and last name are separated by space in name column also confirmed by op in comments 

SELECT  name, COUNT(*) totalCount
FROM    table1
WHERE sex='male'
GROUP   BY SUBSTRING_INDEX(name,' ',1)
ORDER   BY totalCount DESC
LIMIT 1

Fiddle Demo

Upvotes: 2

Related Questions