Oracle SQL 相关子查询:不是单组组函数

Question

我正在尝试计算医生去的旅行并计算通行费的费用。我使用的是 ORACLE XE 11g 和 SQL Developer。我尝试使用 SELECT 中的相关子查询执行此操作，但我收到以下消息：

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

这是我谈到的查询：

SELECT person.name, person.surname, COUNT(DISTINCT travelID) AS numberoftravels, 
  (SELECT SUM(costs.cost) FROM costs
    JOIN travel ON costs.travelID = travel.travelID
    JOIN person ON travel.personID = person.personID
    JOIN staff ON travel.personID = staff.staffID
    WHERE stafftype = 'Doctor' AND costtype = 'tolls' and staff1.staffID = person.personID  
  ) AS tollscost
FROM travel JOIN person ON person.personID = travel.personID
  JOIN staff staff1 ON staff1.staffID = person.personID
WHERE stafftype = 'Doctor'
GROUP BY person.name, person.surname;

在我添加子查询之前，计数工作得很好。有谁明白为什么我会遇到这个问题？

Answer 1

您缺少 GROUP BY 列 - personID