Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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;
share|improve this answer
    
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). –  Colin 't Hart Apr 23 '13 at 7:33
add comment

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

share|improve this answer
    
Syntactically correct, but why have Stage_ID in the select clause? And this will return as many rows as there are Journey_Stages. –  Colin 't Hart Apr 23 '13 at 7:37
    
@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. –  a_horse_with_no_name Apr 23 '13 at 7:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.