Select count / duplicates

Question

Hello!

I have a table with all U.S. zip codes. each row contains the city and state name for the zip code. I'm trying to get a list of cities that show up in multiple states. This wouldn't be a problem if there weren't X amount of zip codes in the same city...

So basically, I just want to the city in a state to count as 1 instead of it counting the city/state 7 times because there are 2+ zip codes in that city/state...

I'm not really sure how to do this. I know I need to use count but how do I tell the mysql to only count a given city/state combo as 1?

Answer 1

Try using a select distinct

SELECT DISTINCT city, state FROM table GROUP BY city

Answer 2

Will this do the trick

Select CityName, Count (Distinct State) as StateCount
From CityStateTable
Group by CityName
HAVING Count (Distinct State) > 1

Answer 3

You probably should have created a separate table for zip codes then to avoid the duplication.

You want to look into the GROUP BY Aggregate.

Answer 4

SELECT City, Count(City) As theCount
FROM (Select City, State From tblCityStateZips Group By City, State) As C
GROUP By City
HAVING COUNT Count(City) > 1

This would return all cities, with count, that were contained in more than one state.

Greenville 39
Greenwood 2
GreenBriar 3
etc.

Answer 5

First group on state and city, then group the result on city:

select City
from (
  select State, City
  from ZipCode
  group by State, City
) x
group by City
having count(*) > 1