Search code examples
sqlpostgresql-9.1correlated-subquery

Select distinct values with count in PostgreSQL

This is a heavily simplified version of an SQL problem I'm dealing with. Let's say I've got a table of all the cities in the world, like this:

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

I want to count how many cities each country has, so that I would end up with a table as such:

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

I know that I can get the distinct country values with SELECT distinct country FROM T1 and I suspect I need to construct a subquery for the city_count column. But my non-SQL brain is just telling me I need to loop through the results...

Thanks!

Solution

  • Assuming the only reason for a new row is a unique city

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