Choose count / duplicates

Question

I have a table with all the zip codes in the USA. each row contains the name of the city and state for the zip code. I am trying to get a list of cities that appear in several states. This would not be a problem if there weren’t X number of postal codes in one city ...

So, I just want the city in the state to be considered 1 instead, counting the city / state 7 times, because in this city / state there are 2 + postal codes ...

I am not sure how to do this. I know that I need to use count, but how can I tell mysql to count only the city / state command as 1?

Answer 1

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 will return all cities with a score that were contained in several states.

Greenville 39
Greenwood 2
GreenBriar 3
and etc.

Answer 2

The first group is by state and city, then group the result by city:

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

Answer 3

Try using dedicated

 SELECT DISTINCT city, state FROM table GROUP BY city 

Answer 4

Will it be a trick

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

Answer 5

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

You want to watch GROUP BY Aggregate.