Count Cities in Database

[mikeybware]

I have a database of customers (MS Access database) and I am trying to check out the demographics. I would like to list the top 5 cities and their count (how many times this city is found in my database), as well as the top 5 postal codes and their count as well.

I can do a count on the select string "SELECT * FROM ContactInfo WHERE City ='Toronto'", but in my case I do not know the names of the top 5 cities.

I am assuming that I would have to iterate through my database and list all of the unique cities and save that to an array or a list of some sort, and then I would have to do a count on each of those city names. This sounds like a lot of processing power, but maybe there is a simpler way of doing this that I am just not seeing.

Any help would be much appreciated.

[AleMonteiro]

It's easier than you think... something like this:

SELECT
    City, Count(CustomerId) as CustomerCount
FROM
    ContactInfo
GROUP BY
    City

[Reverend Jim]

And if you add

ORDER BY CustomerCount DESC

to the end of the query then you will get the records ordered from highest to lowest customer count

[TnTinMN]

Might as well take it the rest of the way and get the top 5 that the OP wants.

SELECT TOP 5 Count(ContactInfo.CustomerID) AS CustomerCount, ContactInfo.City
FROM ContactInfo
GROUP BY ContactInfo.City
ORDER BY Count(ContactInfo.CustomerID) DESC 

Note that this can return more than 5 records if multiple cities have the same count.