>I have two tables, co_location, and phone numbers. They are related by location_id.
>The phone_numbers table has many phone numbers from each city.
>
>I am trying to write a query that will bring up each city that has phone numbers in the phone_number table with something like:
>
>SELECT DISTINCT Co_location.cl_city
> FROM dbo.Co_Location Co_location, dbo.phone_numbers Phone_numbers
> WHERE Phone_numbers.location_id = Co_location.location_id
>
>What I also want is a count for each city of how many numbers there are for that city. so it would look like:
>
>Billings 1000
>Birmingham 500
>Boston 398
>Charlotte 768 *768 being the number of times Charlotte location_id appears in the phone_numbers table
>
>is there any way this can be done?
>Thanks,

Ryan,

Try

SELECT Co_location.cl_city, COUNT(*) AS cnt;
  FROM dbo.Co_Location Co_location, dbo.phone_numbers Phone_numbers;
  WHERE Phone_numbers.location_id = Co_location.location_id;
  GROUP BY Co_location.cl_city