select distinct on 2 fields

Forum Moderators: open

Message Too Old, No Replies

select distinct on 2 fields

cameraguy

4:32 pm on Sep 18, 2006 (gmt 0)

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

7:52 am on Sep 19, 2006 (gmt 0)

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.