0

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.

4
Contributors
3
Replies
27
Views
4 Years
Discussion Span
Last Post by TnTinMN
0

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

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

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

2

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.