title: An SQL query with a WHERE greater than a count, when another field is already in GROUP BY
body: <p>(I gladly welcome better title suggestions)</p>
  
  <p>I'm trying to write an SQL query in Oracle that only outputs entries WHERE one field has a count above a certain value.</p>
  
  <p>Specifically, I have tables AIRPORTS, MARKETS, and STATES.  </p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>MARKETS is information about different markets airports can be in. It contains an ID (primary key) and NAME field. Like so:</p>
  
  <pre><code>30576   Baglung, Nepal
  30577   Binghamton, NY
  30578   Bruggen, Germany
  30579   Bergen, Norway
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>1   ALABAMA  AL
  2   ALASKA   AK
  4   ARIZONA  AZ
  5   ARKANSAS AR
  </code></pre>
  
  <p>I'm trying to write an SQL query that outputs the AIRPORTS.NAME, MARKETS.NAME, and STATES.ABBR fields for all airports in a <strong>market that has airports in more than one state</strong>, 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:</p>
  
  <pre><code>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" &gt; 2
  </code></pre>
  
  <p>But I'm not exactly sure where to go from here. And I'm sure there's a better way to do this.</p>
  
  <p>Thank you!</p>