SQL - Query to display average as either "longer than" or "shorter than"

Posted by user1840801 on Stack Overflow See other posts from Stack Overflow or by user1840801
Published on 2012-11-21T04:23:30Z Indexed on 2012/11/21 5:00 UTC
Read the original article Hit count: 112

Filed under:
|

Here are the tables I've created:

CREATE TABLE Plane_new (Pnum char(3), Feature varchar2(20), Ptype varchar2(15), 
primary key (Pnum));

CREATE TABLE Employee_new (eid char(3), ename varchar(10), salary number(7,2), mid char(3),
PRIMARY KEY (eid), FOREIGN KEY (mid) REFERENCES Employee_new);

CREATE TABLE Pilot_new (eid char(3), Licence char(9), primary key (eid), 
foreign key (eid) references Employee_new on delete cascade);

CREATE TABLE FlightI_new (Fnum char(4), Fdate date, Duration number(2), Pid char(3), 
Pnum char(3), primary key (Fnum), foreign key (Pid) references Pilot_new (eid), 
foreign key (Pnum) references Plane_new);

And here is the query I must complete: For each flight, display its number, the name of the pilot who implemented the flight and the words ‘Longer than average’ if the flight duration was longer than average or the words ‘Shorter than average’ if the flight duration was shorter than or equal to the average. For the column holding the words ‘Longer than average’ or ‘Shorter than average’ make a header Length.

Here is what I've come up with - with no luck!

SELECT F.Fnum, E.ename,
CASE Length 
WHEN F.Duration>(SELECT AVG(F.Duration) FROM FlightI_new F)
THEN "Longer than average"
WHEN F.Duration<=(SELECT AVG(F.Duration) FROM FlightI_new F)
THEN 'Shorter than average' END
FROM FlightI_new F LEFT OUTER JOIN Employee_new E ON F.Pid=E.eid
GROUP BY F.Fnum, E.ename;

Where am I going wrong?

© Stack Overflow or respective owner

Related posts about sql

Related posts about homework