CODEWITHSUNDEEP
oracle-database
PikaJovi
PikaJovi

Reputation: 13

Coding a SUM in a View in Oracle

I am stuck in a progaramming problem in Oracle, that I am trying to solve. I am suppose to create a View that will give me the total hours flown EVER, for crew of the FBN001 on the 27th october 2016

This is the Flight Table:

CREATE TABLE Flight(
Flight_ID VARCHAR(25),
Route_ID VARCHAR(25),
Airplane_ID VARCHAR(10),
Departure_Time DATE,
Arrival_Time DATE,
    CONSTRAINT FlightIDPK PRIMARY KEY(Flight_ID),
    CONSTRAINT RouteIDFK FOREIGN KEY(Route_ID) REFERENCES Route(Route_ID),
    CONSTRAINT AirplaneIDFK FOREIGN KEY(Airplane_ID) REFERENCES Airplane(Airplane_ID));

And this is the Crew Table:

CREATE TABLE Crew(
  Crew_ID NUMBER(10),
  Flight_ID VARCHAR(25),
  Role VARCHAR(25) NOT NULL,
  Employee_ID NUMBER(10),
  Hours NUMBER(10),
    CONSTRAINT CrewIDPK PRIMARY KEY(Crew_ID),
    CONSTRAINT EmployeeIDFK FOREIGN KEY(Employee_ID) REFERENCES Employee(Employee_ID));

I thought i would use the SUM Function:

CREATE VIEW ViewC AS(
SELECT Flight.Route_ID, Crew.Employee_ID, SUM(Hours)AS TotalHoursFlew
FROM Crew, Flight
WHERE Crew.Flight_ID = Flight.Flight_ID
AND Route_ID = 'FBN001');

but that isnt working? what could be the solution? Thank you

Upvotes: 0

Views: 83

Answers (1)

DCookie
DCookie

Reputation: 43523

You need a GROUP BY:

CREATE VIEW ViewC AS(
SELECT Flight.Route_ID, Crew.Employee_ID, SUM(Hours)AS TotalHoursFlew
FROM Crew, Flight
WHERE Crew.Flight_ID = Flight.Flight_ID
AND Route_ID = 'FBN001'
GROUP BY Flight.Route_ID, Crew.Employee_ID);

Upvotes: 1

Related Questions