Select distinct values with count in PostgreSQL

sql postgresql-9.1 correlated-subquery
durrrutti picture durrrutti · Jun 3, 2014 · Viewed 10.1k times · Source

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!

Answer

MarkD picture MarkD · Jun 3, 2014

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

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

Related questions

must appear in the GROUP BY clause or be used in an aggregate function

I have a table that looks like this caller 'makerar' cname | wmname | avg --------+-------------+------------------------ canada | zoro | 2.0000000000000000 spain | luffy | 1.00000000000000000000 spain | usopp | 5.0000000000000000 And I want to select the maximum avg for each cname. SELECT cname, wmname, MAX(avg) FROM makerar …

Read More
How to use SQL LIKE condition with multiple values in PostgreSQL?

Is there any shorter way to look for multiple matches: SELECT * from table WHERE column LIKE "AAA%" OR column LIKE "BBB%" OR column LIKE "CCC%" This questions applies to PostgreSQL 9.1, but if there is a generic solution it would be …

Read More
Strange PostgreSQL "value too long for type character varying(500)"

I have a Postgres schema which looks like: The problem is that whenever I save text longer than 500 characters in the description column I get the error: value too long for type character varying(500) In the documentation for Postgres it …

Read More