Names with Multiple Locations

[netrusher]

I have a table with two fields.

1. Name
2. Location

Each name will have at least one location. Some names will have multiple locations (2-4).
What I want to accomplish is to find out how many distinct names I have and also to get
a count of how many names that have multiple locations. Ideally I would like to see each Name
with each location under it as well as have the count of distinct names and the count of
names with multiple locations.

Any thoughts and or suggestions?

 

[PHV]

the count of distinct names
SELECT Count(*) AS NumOfDictinctNames FROM (
SELECT DISTINCT [Name] FROM yourTable
) AS D
the count of names with multiple locations
SELECT Count(*) AS NumOfMultipleLoc FROM (
SELECT [Name] FROM yourTable GROUP BY [Name] HAVING Count(*) > 1
) AS M

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244