SQL GROUP BY two different group

Question

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

Answer 1

You've very close, you just need a HAVING clause, which is like a WHERE clause for group by-aggregated metrics.

SELECT CITY
FROM MAP
GROUP BY COAST, REGION
HAVING COUNT(*) = 1;

---

Edit: Here's the SQL Fiddle link: http://sqlfiddle.com/#!9/8da83b/2/0

---

Edit 3: This seems needlessly complicated, but it works and avoids using any fields that aren't grouped.

SELECT City
FROM Map
INNER JOIN (
  SELECT Coast, Region, COUNT(*) as RowCount
  FROM Map
  GROUP BY Coast, Region
) AS CoastRegion ON
  Map.Coast = CoastRegion.Coast AND
  Map.Region = CoastRegion.Region 
WHERE
  CoastRegion.RowCount = 1

As an aside, it seems pretty silly to use StackOverflow to solve your HackerRank problems. I'm including this as a learning exercise ;)