condense logic down to one Sql query

first is the disclaimer, I did not design the database I am now supporting and I would not have designed it this way, but here I am.

Problem: My client has a table in his database that contains listings for rentals. In this table is a text field to store the city of the listing. What my client is hoping to see is a dropdown list of all the unique cities with a nu开发者_高级运维mber next to each one representing how many listings are in that city.

Ignoring for the time being spelling and capitalization issues, what Sql should I use to get the unique list along with each items count? For a proof of concept I queried for the distinct list of cities (easy enough), and then iterated through each one querying the number of times it is in the table. This is obviously horribly inefficient.

Can someone help me with the Sql to do all this in one statement?

SELECT
 CityName,
 COUNT(*) AS CityListings
FROM
 YourTable
GROUP BY
 CityName
ORDER BY
 CityName

SELECT CITY, COUNT (*) FROM TABLE GROUP BY CITY

;) Get a good SQL Book, This is "SQL in 21 hours" level ;)

select CityName, COUNT(*) from CitiesTable

group by CityName

You are looking for the Group By clause:

SELECT city, COUNT(city)
FROM rentals
GROUP BY city
ORDER BY city

To start with, try this

SELECT City, Count(*)
FROM myTable
GROUP BY City

Do you mean something like the following?

select city, count(city) from listings
group by city

This should turn:

Shepparton blah blah blah
Mildura    yada yada yada
Shepparton dum  de   dum

into:

Shepparton 2
Mildura    1

which is what it sounds like you're after.

SELECT DISTINCT city_name, count(*)
FROM rentals
GROUP BY city_name