Selecting individual values ​​with count in PostgreSQL

Question

This is a very simplified version of the SQL problem I'm dealing with. Let's say I have a table of all cities in the world, for example:

country city
------------
Canada  Montreal
Cuba    Havanna
China   Beijing
Canada  Victoria
China   Macau

I want to calculate how many cities are in each country, so I get a table as such:

country city_count
------------------
Canada  50
Cuba    10
China   200

I know that I can get different country values ​​using SELECT distinct country FROM T1, and I suspect that I need to build a subquery for the city_count column. But my non-SQL brain just tells me that I need to punch results ...

Thank!

Answer 1

Assuming the only reason for the new line is a unique city

select country, count(country) AS City_Count
from table
group by country