Select distinct values with count in PostgreSQL
correlated-subquery, postgresql-9.1, sql
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
Problem
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!