group records which contain string

Question

I've a table with following data:

+----+-----------------+
| id | country         |
+----+-----------------+
|  1 | i'm from usa    |
|  2 | i'm from italy  |
|  3 | i'm from china  |
|  4 | i'm from india  |
|  5 | she's from usa  |
|  6 | he's from china |
+----+-----------------+

I want to know the population of each country, by checking country name in the country column. I want something like this:

+---------+------------+
| country | population |
+---------+------------+
| usa     | 2          |
| italy   | 1          |
| china   | 2          |
| india   | 1          |
+---------+------------+

I think I should use GROUP BY and COUNT() function. But How? Thanks.

Answer 1

If the country is always at the end you can use this.

select
  case 
    when country like '%usa' then 'usa'
    when country like '%italy' then 'italy'
    when country like '%china' then 'china'
    when country like '%india' then 'india'
  end as ccountry,
  count(*) as population
from Table1
group by ccountry;

If country can be anywhere in the string you can find it like this assuming it is at the beginning, at the end or in the middle surrounded by space.

select
  case 
    when country like '% usa %' then 'usa'
    when country like '% italy %' then 'italy'
    when country like '% china %' then 'china'
    when country like '% india %' then 'india'
  end as ccountry,
  count(*) as population
from 
    (
      select concat(' ', country, ' ') as country
      from Table1
    ) T
group by ccountry