An SQL query with a WHERE greater than a count, when another field is already in GROUP BY

Question

(I gladly welcome better title suggestions)

I'm trying to write an SQL query in Oracle that only outputs entries WHERE one field has a count above a certain value.

Specifically, I have tables AIRPORTS, MARKETS, and STATES.

The AIRPORTS table is information about airports with fields ID (primary key), NAME, ABBR (abbreviation), MARKET (foreign key from MARKETS table), STATE (foreign key from STATE table), and CITY. Like so:

14122   PITTSBURGH INTERNATIONAL    PIT 30198   42  PITTSBURGH, PA
14150   PELLSTON REGIONAL AIRPORT   PLN 34150   26  PELLSTON, MI
14193   PENSACOLA GULF COAST REGIONAL   PNS 33728   12  PENSACOLA, FL

MARKETS is information about different markets airports can be in. It contains an ID (primary key) and NAME field. Like so:

30576   Baglung, Nepal
30577   Binghamton, NY
30578   Bruggen, Germany
30579   Bergen, Norway

STATES contains information about states in the USA, using the government's FIPS codes. It contains fields FIPS (primary key), NAME, and ABBR (abbreviation). Like so:

1   ALABAMA  AL
2   ALASKA   AK
4   ARIZONA  AZ
5   ARKANSAS AR

I'm trying to write an SQL query that outputs the AIRPORTS.NAME, MARKETS.NAME, and STATES.ABBR fields for all airports in a market that has airports in more than one state, and I'd like to do it without creating a view. I've gotten as far as a query that shows me all the MARKETS.ID with more than 2 airports:

SELECT *
FROM(
SELECT markets.id as "market", count(markets.name) as "airports"
FROM markets
INNER JOIN airports
ON airports.market = markets.id
GROUP BY markets.id) 
WHERE "airports" > 2

But I'm not exactly sure where to go from here. And I'm sure there's a better way to do this.

Thank you!

Answer 1

  SELECT m.id AS market, COUNT(*) AS airports
  FROM markets AS m
    INNER JOIN airports AS a
    ON a.market = m.id
  GROUP BY m.id
  HAVING COUNT(*) > 2

Answer 2

SELECT airport, market, state
FROM (
  SELECT airports.name AS airport
        ,markets.name AS market
        ,states.abbr AS state
        ,count(DISTINCT airports.state) OVER (PARTITION BY airports.market)
         AS states_per_market
  FROM   airports
  JOIN   markets
  ON     airports.market = markets.id
  JOIN   states
  ON     airports.state = states.fips
) WHERE states_per_market > 1;