condense logic down to one Sql query

Question

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 number 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?

Answer 1

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

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

Answer 2

You are looking for the Group By clause:

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

Answer 3

To start with, try this

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

Answer 4

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.

Answer 5

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

Answer 6

select CityName, COUNT(*) from CitiesTable

group by CityName

Answer 7

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