EDITED: I am trying to CASE a query from my table when a result meets 2 sets of criteria (Flight Time & Tail Number), but I am receiving the wrong result. I only started SQL a few weeks ago for an upcoming school assignment, but am having trouble with this query.

SELECT FlightNumber AS 'Flight Number', Date, Aircraft, Aircraft_Manufacturer AS 'Aircraft Manufacturer', Tail_Number AS 'Tail Number', Departure, Arrival, FlightTime AS 'Flight Time', Instructor, Passengers, 
CASE
    WHEN SUM(FlightTime) >= 20 AND Tail_Number LIKE '24-%' THEN "RAAus Recreational Pilots Certificate (RPC)"
    WHEN SUM(FlightTime) >= 25 AND Tail_Number LIKE '24-%' THEN "RAAus RPC Passenger Endorsment"
    WHEN SUM(FlightTime) >= 32 AND Tail_Number LIKE '24-%' THEN "RAAus RPC Cross Country Endorsment"
    WHEN SUM(FlightTime) >= 7 AND Tail_Number LIKE 'VH-%' THEN "RPC Conversion to CASA Recreational Pilot License (RPL)"
    ELSE 'Not Eligible For License'
END AS "License Eligibility"
FROM Flight_Log
GROUP BY FlightNumber
ORDER BY FlightNumber

My results are:

Expected Results:

Once the cumulative hours reaches 20 or more to show the WHEN statements.

My Table Creation with the INSERT INTO looks like this for anyone wondering (I have removed a few rows from the end):

CREATE TABLE IF NOT EXISTS Flight_Log (
    `FlightNumber` INTEGER PRIMARY KEY AUTO_INCREMENT,
    `Date` DATE,
    `Aircraft` VARCHAR(50),
    `Aircraft_Manufacturer` VARCHAR(50),
    `Tail_Number` VARCHAR(50),
    `MTOW` INTEGER,
    `Manufacture_Year` YEAR,
    `Aircraft_Type` VARCHAR(50),
    `Departure` CHAR(4),
    `Arrival` CHAR(4),
    `FlightTime` INTEGER,
    `Instructor` BOOLEAN,
    `Passengers` INTEGER
);
INSERT INTO Flight_Log VALUES (1,'2022-10-08','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',1,'1',0);
INSERT INTO Flight_Log VALUES (2,'2022-12-03','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',3,'1',0);
INSERT INTO Flight_Log VALUES (3,'2022-12-05','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',3,'1',0);
INSERT INTO Flight_Log VALUES (4,'2022-12-08','Tecnam Sierra','Tecnam','24-7155',600,2002,'Recreational','YCDR','YCDR',3,'1',0);
INSERT INTO Flight_Log VALUES (5,'2022-12-17','Tecnam Sierra','Tecnam','24-7155',600,2002,'Recreational','YCDR','YCDR',3,'1',0);
INSERT INTO Flight_Log VALUES (6,'2022-12-27','Fly Synthesis Texan','Fly Synthesis','24-5285',600,1998,'Recreational','YCDR','YCDR',1,'1',0);
INSERT INTO Flight_Log VALUES (7,'2022-12-31','Tecnam Sierra','Tecnam','24-7155',600,2002,'Recreational','YCDR','YCDR',2,'0',0);
INSERT INTO Flight_Log VALUES (8,'2023-01-01','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',2,'0',0);
INSERT INTO Flight_Log VALUES (9,'2023-01-12','Tecnam Sierra','Tecnam','24-7155',600,2002,'Recreational','YCDR','YCDR',1,'0',0);
INSERT INTO Flight_Log VALUES (10,'2023-01-18','Tecnam Sierra','Tecnam','24-7155',600,2002,'Recreational','YCDR','YCDR',4,'0',0);
INSERT INTO Flight_Log VALUES (11,'2023-01-27','Tecnam Sierra','Tecnam','24-7155',600,2002,'Recreational','YCDR','YCDR',3,'0',0);
INSERT INTO Flight_Log VALUES (12,'2023-02-03','Tecnam Sierra','Tecnam','24-7155',600,2002,'Recreational','YCDR','YCDR',3,'0',0);
INSERT INTO Flight_Log VALUES (13,'2023-02-14','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',1,'0',1);
INSERT INTO Flight_Log VALUES (14,'2023-02-14','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',1,'0',1);
INSERT INTO Flight_Log VALUES (15,'2023-03-14','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',1,'0',1);
INSERT INTO Flight_Log VALUES (16,'2023-03-14','Tecnam P-92 Eaglet','Tecnam','24-5955',600,1992,'Recreational','YCDR','YCDR',1,'0',1);
INSERT INTO Flight_Log VALUES (17,'2023-03-28','Cessna 172R','Cessna','VH-CFG',1111,1997,'Recreational','YCDR','YCDR',3,'1',0);
INSERT INTO Flight_Log VALUES (18,'2023-04-06','Cessna 172 G1000','Cessna','VH-IVW',1156,2013,'Recreational','YRED','YRED',3,'1',0);
INSERT INTO Flight_Log VALUES (19,'2023-04-19','Cessna 172 G1000','Cessna','VH-IVW',1156,2013,'Recreational','YRED','YCDR',2,'1',0);
INSERT INTO Flight_Log VALUES (20,'2023-04-23','Cirrus SR22','Cirrus','VH-EDH',1633,2014,'Recreational','YBAF','YBAF',2,'1',0);
INSERT INTO Flight_Log VALUES (21,'2023-05-02','Cirrus SR22','Cirrus','VH-EDH',1633,2014,'Recreational','YBAF','YBAF',2,'1',0);

What am I doing wrong? (I am using the newest version of MySQL (8.0.31))

CodePudding user response：

When you are mixing aggregate functions (SUM in your case) and plain columns in select you will need a GROUP BY statement telling over which set the aggregate function is calculated from.

You are also using FlightTim and sum(FlightTime) at same level. Over which set do you want the sum(FlightTime) to be calculated?

Modify the question to include sample data with expected results.

CodePudding user response：

So I recreated your table:

INSERT INTO public.flights(
    flight_number, flight_time, tail_number)
    VALUES 
        (1, 20, '24-5955'),
        (2, 25, '24-5874'),
        (3, 32, '24-1111'),
        (4, 7, 'VH-1234'),
        (5, 5, '24-5955'),
        (6, 8, '24-9874'),
        (7, 25, '24-5555');

Which gives me this:

Query you should have:

select f2.flight_number, f2.flight_time, f1.tail_number, 
    CASE 
        WHEN f1.flight_time >= 20 AND f1.flight_time < 25 AND f1.tail_number LIKE '24-%' THEN 'RAAus Recreational Pilots Certificate (RPC)'
        WHEN f1.flight_time >= 25 AND f1.flight_time < 32 AND f1.tail_number LIKE '24-%' THEN 'RAAus RPC Passenger Endorsment'
        WHEN f1.flight_time >= 32 AND f1.tail_number LIKE '24-%' THEN 'RAAus RPC Cross Country Endorsment'
        WHEN f1.flight_time >= 7 AND f1.tail_number LIKE 'VH-%' THEN 'RPC Conversion to CASA Recreational Pilot License (RPL)'
        ELSE 'Eligibility Checker Error'
    END as "License Eligibility"
FROM (SELECT sum(flight_time) as flight_time, tail_number FROM flights group by tail_number) f1
INNER JOIN flights f2
on f1.tail_number = f2.tail_number
group by f2.flight_number, f1.tail_number, f1.flight_time
order by flight_number

this gives me:

Just add the other columns you have with its alias' and you should be good. If this isn't what you want, please provide the exercise statement.

For your DATASET this query will be:

select flightnumber as "Flight Number", "Date", aircraft, Aircraft_Manufacturer AS "Aircraft Manufacturer", f1.Tail_Number AS "Tail Number", Departure, Arrival, FlightTime AS "Flight Time", Instructor, Passengers, 
    CASE 
        WHEN f1.flight_time >= 20 AND f1.flight_time < 25 AND f1.tail_number LIKE '24-%' THEN 'RAAus Recreational Pilots Certificate (RPC)'
        WHEN f1.flight_time >= 25 AND f1.flight_time < 32 AND f1.tail_number LIKE '24-%' THEN 'RAAus RPC Passenger Endorsment'
        WHEN f1.flight_time >= 32 AND f1.tail_number LIKE '24-%' THEN 'RAAus RPC Cross Country Endorsment'
        WHEN f1.flight_time >= 7 AND f1.tail_number LIKE 'VH-%' THEN 'RPC Conversion to CASA Recreational Pilot License (RPL)'
        ELSE 'Eligibility Checker Error'
    END as "License Eligibility"
FROM (SELECT sum(flighttime) as flight_time, tail_number as tail_number FROM Flight_Log group by Tail_Number) f1
INNER JOIN Flight_Log f2
on f1.tail_number = f2.Tail_Number
group by flightnumber, f1.tail_number, f1.flight_time
order by flightnumber

IMPORTANT: It is normal that the License Eligibility is an Error everywhere... When running this query:

select sum(flighttime), tail_number from Flight_Log
group by tail_number

we can see that all flightnumbers with tail number starting with 24 are under 20 hours --> So error And also the tailnumbers with VH are all under 7 --> Error. If you add more data and more hours to all tail_numbers you will see my query works