select distinct on 2 fields

[cameraguy]

Hello!

I am trying to consolidate data from a table that has duplicates:

date ¦ longitude ¦ latitude ¦ city ¦ visitors
2006-09-07 ¦ 38.7167 ¦ -9.1333 ¦ Lisbon ¦ 5
2006-09-15 ¦ 38.7167 ¦ -9.1333 ¦ Lisbon ¦ 12
2006-09-18 ¦ 38.5000 ¦ -1.5000 ¦ Paris ¦ 9
2006-09-22 ¦ 38.7167 ¦ -9.1333 ¦ Lisboa ¦ 6

My objective is to obtain a consolidated table by longitude and latitude:
longitude ¦ latitude ¦ city ¦ visitors
38.7167 ¦ -9.1333 ¦ Lisbon ¦ 23
38.5000 ¦ -1.5000 ¦ Paris ¦ 9

Notice that I cannot group by country since I have several spellings for a given city (Lisbon and Lisboa).

I tried to select by distinct longitude and latitude, but without success. "SELECT distinct latitude, longitude" lists duplicated coordinates and any grouping thereof is al unsuccessful.

Any idea?

Thanks

[FalseDawn]

SELECT longitude,latitude,MIN(city),SUM(visitors) FROM yourtable GROUP BY longitude,latitude

As the cities are different, there's no ideal way to select any particular city to represent the "group", so the query above just selects the "first" one.

If this is an issue, I'd suggest doing some data scrubbing beforehand on the city names - this is the preferable route, to be honest.