Oracle SUM – ORA-00937: not a single-group group function

oracle

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.

Best Answer

If you want the distance per Journey, you need to group by Journey:

SELECT Journey.Journey_No, 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
GROUP BY Journey.Journey_No;

Compare this to Option #2 that Rolando posted and you'll notice I removed one column each from the SELECT and GROUP BY clauses.

Related Question