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.

Recommended Answers

All 3 Replies

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

    City, Count(CustomerId) as CustomerCount

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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.