Take the 2-minute tour ×

I searched around and found some similar questions, but nothing that quite solved my problem. I'll try to explain this accurately.

So I have a bunch of data for an organization in a MySQL Database, and one of the fields in a table refers to our partner organizations in different countries. The form of this is something like:

Company1 (China)

Company2 (United States)

Company3 (China)

I am trying to find the number of partners in each country using COUNT(*) and GROUP BY, but I can't figure out how to group by only the part of the data containing the country, ignoring the company name before it. The closest thing I've found used Oracle's regexp_replace function, but I haven't been able to find a replacement in MySQL.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

If you have a list of countries, you can do something like:

select c.country, count(*)
from t join
     countries c
     on t.company like concat('%(', c.country, ')%')
group by c.country
share|improve this answer
A side note: just to improve readability it may make sense to swap from and join tables –  zerkms Mar 22 '13 at 1:07
Great Idea, worked perfectly. Thanks! –  trevwilson Mar 22 '13 at 1:27
add comment

Your Answer


