Oracle ORA-00937:不是单个组函数

Question

每一次旅行都想把它们相加，计算出两者之间的距离。

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;

表如下：

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

但是，我得到了以下错误：

ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"
*Cause:    
*Action: Error at Line: 273 Column: 26

我相信这是一个很容易解决的问题。

Answer 1

如果您想要每次旅行的距离，您需要group by旅程：

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;

将其与Rolando发布的选项2进行比较，您将注意到我从SELECT和GROUP BY子句中各删除了一列。

Answer 2

你应该做两件事之一：

选项1:只保留聚合列

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;

选项2:添加一个GROUP BY子句

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;

Answer 3

选项#3使用窗口函数：

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;

但我想你可能想要罗兰多的第二选择