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

Question

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.

Answer 1

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.

Answer 2

You should do one of two things:

OPTION #1: Keep only aggregate columns

SELECT 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;

OPTION #2: Add a GROUP BY clause

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

• Colin 't Hart: Technically correct but this doesn't explain the functional difference between the two options. Option #1 will give you the TOTAL sum of "Stages.Distance_Between" across all Journeys. Option #1 will give you the sum of "Stages.Distance_Between" for all Stages within Journeys (which is probably semantically incorrect because a the distance is already recorded at the stage level, there is no need to sum it).

Answer 3

Option #3 Use a window function:

SELECT Journey.Journey_No, 
 Stages.Stage_ID, 
 SUM(Stages.Distance_Between) over (partition by Journey.Journey_No) as dist_sum
FROM Journey
 JOIN Journey_Stages ON Journey.Journey_No=Journey_Stages.Journey_No
 JOIN Stages ON Journey_Stages.Stage_ID=Stages.Stage_ID;

But I think you probably want Rolando's Option #2

• Colin 't Hart: Syntactically correct, but why have Stage_ID in the select clause? And this will return as many rows as there are Journey_Stages.
• user1822: @Colin'tHart: it's not clear for me from the original question what David wants. So I copied all the columns from the original SELECT. I agree that it's very likely that this is not what he is after - your answer is probably the way to go.