Select distinct values with count in PostgreSQL

Question

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!

http://stackoverflow.com/questions/11807386/group-by-and-count-in-postgresql — Bharadwaj, Jun 03 '14 at 14:25

score 35 · Accepted Answer · answered Jun 03 '14 at 14:30

35

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

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

answered Jun 03 '14 at 14:30

MarkD

1,043
12
18

Well, that wasn't too difficult. Thank you! – durrrutti Jun 03 '14 at 14:37
1

There are a few aggregate functions like count that come in very handy and are worth knowing to allow you to pull out the key information about the data you have(count brings back the number of occurrences of a value, max gives you the highest value etc) Try this link http://www.w3schools.com/sql/sql_functions.asp – MarkD Jun 03 '14 at 14:40

Select distinct values with count in PostgreSQL

1 Answers1

Linked