CODEWITHSUNDEEP
mysql
HyderA
HyderA

Reputation: 21391

MySQL Query: display the number of posts for each unique city

I'm not very good at database queries. Need help with what is probably a simple query.

Database: MYSQL

zipcodes [table]

zip | city | state

post [table]

post_id | title | post | zip

I need to display the number of posts for each unique city.

Upvotes: 1

Views: 73

Answers (2)

TechEnthusiast
TechEnthusiast

Reputation: 1895

I would do it this way. This would display the city name in the output and also show teh cities for which there are no posts exist.

SELECT city,count(*) as "num_posts" FROM zipcodes LEFT JOIN post ON post.zip = zipcodes.zip GROUP BY city;

Upvotes: 0

Marius
Marius

Reputation: 58949

SELECT count(*) FROM post LEFT JOIN zipcodes ON post.zip = zipcodes.zip GROUP BY city;

Upvotes: 3

Related Questions