For every journey want to sum them to work out the distance between.
SELECT Journey.Journey_No, Stages.Stage_ID, SUM(Stages.Distance_Between) FROM Journey, Journey_Stages, Stages WHERE Journey.Journey_No=Journey_Stages.Journey_No AND Journey_Stages.Stage_ID=Stages.Stage_ID;
Tables are as followed:
CREATE TABLE Journey_Stages(Journey_No integer REFERENCES Journey(Journey_No),Stage_ID integer REFERENCES Stages(Stage_ID)); CREATE TABLE Stages(Stage_ID integer PRIMARY KEY, Start_Station integer REFERENCES Stations(Station_ID), End_Station integer REFERENCES Stations(Station_ID)); CREATE TABLE Journey(Journey_No integer PRIMARY KEY, Train_No integer REFERENCES Train(Train_No));
However I am getting the following error:
ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function" *Cause: *Action: Error at Line: 273 Column: 26
I am certain this is something simple to resolve.