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

count, join, oracle, sql, subquery

Solution

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;

Problem

(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!

Original source