CODEWITHSUNDEEP
mysqlsql
Computer User
Computer User

Reputation: 2869

Find total count based of values from another table

In Mysql:

I have cities in table with duplicate values:

Table Cities:

Name

New York, USA
New York, USA
Chicago, USA
Chicago, USA
Chicago, USA
Paris, France
Nice, France
Milan, Italy

The format of data in this table is:

<city>, <country>

Same as:

<city><comma><space><country>

Table Country:

Name

USA
France
Italy

I want to get how many cities each country has. Like:

Country      Count

USA           2
France        2
Italy         1

So, I have 2 queries:

SELECT count(*) FROM `Cities` WHERE Cities.name LIKE '%, USA%'

But this value USA should come from:

SELECT * FROM `country`

Now, how do I get the desired table?

Upvotes: 0

Views: 216

Answers (1)

forpas
forpas

Reputation: 164089

With a LEFT JOIN:

select t1.name Country, count(distinct t2.name) Count
from country t1 left join cities t2 
on t2.name like concat('%, ', t1.name)
group by t1.name

See the demo.
Results:

| Country | Count |
| ------- | ----- |
| France  | 2     |
| Italy   | 1     |
| USA     | 2     |

Upvotes: 2

Related Questions