SQL Query with a count condition

sql query for finding records where count > 1
count number of rows in sql query result
conditional count sql
sql count if
select and count in one query sql
sql count if greater than
sql count multiple columns
sql count(distinct)

Here is my table structure:

CREATE TABLE CITY(
  CITY_ID NUMBER(3) CONSTRAINT CITY_ID_PK PRIMARY KEY,
  CITY_NAME VARCHAR2(20) CONSTRAINT CITY_NAME_NN NOT NULL);

CREATE TABLE PILOT(
  PILOT_ID NUMBER(3) CONSTRAINT PILOT_ID_PK PRIMARY KEY,
  LAST_NAME VARCHAR2(20) CONSTRAINT LAST_NAME_NN NOT NULL,
  FIRST_NAME VARCHAR2(20) CONSTRAINT FIRST_NAME_NN NOT NULL,
  CITY_ID NUMBER(3) CONSTRAINT CITY_ID_FK REFERENCES CITY(CITY_ID),
  SALARY NUMBER(7,2) CONSTRAINT SALARY_CK CHECK (SALARY >= 5000 AND SALARY <= 7000));

CREATE TABLE PLANE(
  PLA_ID NUMBER(2) CONSTRAINT PLANE_ID_PK PRIMARY KEY,
  PLA_DESC VARCHAR2(20) CONSTRAINT PLANE_DESC_NN NOT NULL,
  MAX_PASSENGER NUMBER(3),
  CITY_ID NUMBER(3) CONSTRAINT PLANE_CITY_ID_FK REFERENCES CITY(CITY_ID),
  CONSTRAINT MAX_PASSENGER_CK CHECK (MAX_PASSENGER <= 500));

CREATE TABLE FLIGHT(
  FLIGHT_ID NUMBER(3) CONSTRAINT FLIGHT_ID_PK PRIMARY KEY,
  PILOT_ID NUMBER(3) CONSTRAINT FLIGHT_PILOT_ID_FK REFERENCES PILOT(PILOT_ID),
  PLA_ID NUMBER(2) CONSTRAINT FLIGHT_PLA_ID_FK REFERENCES PLANE(PLA_ID),
  CITY_DEP NUMBER(3) CONSTRAINT FLIGHT_CITY_DEP_FK REFERENCES CITY(CITY_ID),
  CITY_ARR NUMBER(3) CONSTRAINT FLIGHT_CITY_ARR_FK REFERENCES CITY(CITY_ID),
  DEP_DATE DATE,
  DEP_TIME NUMBER(4),
  ARR_TIME NUMBER(4),
  CONSTRAINT ARR_TIME_CK CHECK (ARR_TIME > DEP_TIME));

The question I have in this lab is to display pilots (ID and Name) who perform two or more flights out of Montreal (It is required that I use the city name in the query and not the ID)

Here is what I have come up with so far:

SELECT PILOT_ID, LAST_NAME, FIRST_NAME
FROM PILOT
JOIN FLIGHT USING (PILOT_ID)
WHERE CITY_DEP=(SELECT CITY_ID
                FROM CITY
                WHERE CITY_NAME='MONTREAL')

Obviously this gets me part of the answer, but it is not displaying exactly the information I need which is simply the pilots who make this fight >= 2 times.

You can use FETCH ROWS

SELECT PILOT_ID, LAST_NAME, FIRST_NAME
FROM PILOT
JOIN FLIGHT USING (PILOT_ID)
WHERE CITY_DEP=(SELECT CITY_ID
                FROM CITY
                WHERE CITY_NAME='MONTREAL')
FETCH FIRST 2 ROWS ONLY

SQL COUNT() with HAVING, How do I get row count in SQL Select query? Get a wide range of information from across the web with Simpli.com.

Edited with new information on data structure

Understanding your goal

I believe I understand your goal to be querying pilot level data for pilots who have departed from Montreal at least twice in one day.

Query Solution

If my assumptions are true, I believe you can meet your needs by doing something similar to this:

CREATE GLOBAL TEMPORARY TABLE flight_per_day ON COMMIT PRESERVE ROWS AS
SELECT
       p.pilot_id,
       f.dep_date,
       COUNT(CASE WHEN c.city_name = 'MONTREAL' THEN 1 ELSE NULL END) as 
        montreal_cnt
  FROM flights f
       LEFT JOIN pilot p ON p.pilot_id = f.pilot_id
       LEFT JOIN city c on f.city_dep = c.city_id
 GROUP BY 1, 2;


    SELECT
           p.pilot_id,
           p.first_name,
           p.last_name
      FROM flight_per_day fp
           LEFT JOIN pilot p ON p.pilot_id = fp.pilot_id
     WHERE fp.montreal_cnt>=2

or without a temp table you could do

SELECT
       p.pilot_id,
       p.first_name,
       p.last_name
  FROM
       (SELECT
              p.pilot_id,
              f.dep_date,
              -- Find the total number of flights (COUNT) where (CASE WHEN) a flight departs from Montreal (THEN) count it otherwise (ELSE) ignore it (NULL)
              COUNT(CASE WHEN c.city_name = 'MONTREAL' THEN 1 ELSE NULL END) as 
                montreal_cnt
         FROM flights f
              -- Join in pilot table to get the counts by pilot_id
              LEFT JOIN pilot p ON p.pilot_id = f.pilot_id
              -- Join in city table to get city_name instead of city_id
              LEFT JOIN city c on f.city_dep = c.city_id
        GROUP BY 1, 2) fp
       LEFT JOIN pilot p ON p.pilot_id = fp.pilot_id
-- Only give me the data for pilots who have flown out of Montreal at least twice in one day
 WHERE fp.montreal_cnt>=2

COUNT (Transact-SQL), SQL COUNT() with HAVING: The HAVING clause with SQL COUNT() function can be used to set a condition with the select statement. Looking for how to learn sql and related content? Search now! Discover how to learn sql. Get your questions answered.

For each pilot, you need to count how many flights that pilot has from Montreal, and then retrieve the pilots that have 2 or more flights. This is a job for GROUP BY and HAVING.

SELECT PILOT_ID, LAST_NAME, FIRST_NAME
FROM PILOT
JOIN FLIGHT USING (PILOT_ID)
JOIN CITY ON (CITY_DEP = CITY_ID)
WHERE CITY_NAME='MONTREAL'
GROUP BY PILOT_ID, LAST_NAME, FIRST_NAME
HAVING COUNT(*) >= 2;

Getting Row Counts in MySQL (part 1), If you can't just limit the query itself with a where clause, you can use the fact select count(case Position when 'Manager' then 1 else null end) from . You can also use the Pivot Keyword if you are using SQL 2005 or above. SQL Server implies an else null for case statements, so the count() example can be 10 characters shorter (if you count the space). – Michael - Where's Clay Shirky Feb 16 '18 at 22:05 add a comment |

Is it possible to specify condition in Count()?, In summary: COUNT(*) counts the number of items in a set. COUNT(ALL expression) evaluates the expression for each row in a set and returns the number of non-null values. COUNT(DISTINCT expression) evaluates the expression for each row in a set, and returns the number of unique, non-null values. The HAVING clause with SQL COUNT() function can be used to set a condition with the select statement. The HAVING clause is used instead of WHERE clause with SQL COUNT() function. The GROUP BY with HAVING clause retrieves the result for a specific group of a column, which matches the condition specified in the HAVING clause.

SQL Server COUNT Function By Practical Examples, We can do that by putting the conditional in a case statement, and Next time you need to count based on a condition, try using sum(case…)  The SQL COUNT function is an aggregate function that returns the number of rows returned by a query. You can use the COUNT function in the SELECT statement to get the number of employees, the number of employees in each department, the number of employees who hold a specific job, etc. The following illustrates the syntax of the SQL COUNT function: COUNT([ALL | DISTINCT] expression); The result of the COUNT function depends on the argument that you pass to it.

Counting Conditionally in SQL, This SQL tutorial explains how to use the SQL COUNT function with syntax, SELECT COUNT(aggregate_expression) FROM tables [WHERE conditions]  COUNT () function. The SQL COUNT () function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT () returns 0 if there were no matching rows.

Comments
  • I don't see any column for the date. You might want to show us the structure of all tables.
  • I'm going to further detail my question. First time using site, sorry for that.
  • Without trying it... something like this, though perhaps not terribly efficient... Flight left join City left Join Pilot where City=Montreal group by City, Pilot having count > 2
  • @Julien thanks for the data structure info, I edited the code below accordingly and believe that should get you where you need to be. To be able to know the amount of times per day a pilot departs, you'll need to wrap a CASE WHEN with your conditional (that the departure city be Montreal) into a COUNT to count the amount of times each pilot meets that conditional.
  • I don't believe this accomplishes the goal of aggregating a non-aggregated data set. This is essentially an Oracle limit clause that will just pull the top two rows that meet the query conditions. I believe OP has a table with one row for all flights, and wants to find pilots who have at least two rows of data where city_dep = 'MONTREAL'.