SQL GROUP BY two different group

Viewed 87

Is it possible to list the city name which only contains unique Coast and Region? (i.e. there are multiple cities in the same coast and region, thus they should be excluded)

Example:

TABLE_NAME = MAP
City        Coast       Region
New York    East        1
Buffalo     East        1
LA          West        2
Seattle     West        1
San Jose    West        3
Florida     East        4
Boston      East        2
San Diego   West        2

In this example, I want the output to be:

Seattle
San Jose
Florida
Boston

New York and Buffalo are omitted because they share the same coast and region, just like LA and San Diego.

I tried:

SELECT DISTINCT COAST, REGION 
FROM MAP

which gave me the Cost and Region, but not the name of the city.

I tried:

SELECT CITY 
FROM MAP 
GROUP BY COAST, REGION

But it gave me an error (see error below).

Thanks for any help!

EDIT: I tried adding the HAVING clause, but it still gave me the same error as before. This is the error I had:

ERROR 1055 (42000); Expression #1 of SELECT list is not in GROUP BY clause and contain nonaggregated column run_iu98jon.MAP.CITY which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

1 Answers
Related