PGError: 错误：列“p.name”必须出现在GROUP BY子句中或用于聚合函数。

Question

我使用这个查询时遇到了错误。为什么？我不明白 :(

    SELECT p.name, p.id, SUM(hours) AS hours, SUM(logged_hours) AS logged_hours
    FROM (
        SELECT project_id, date, hours, null AS logged_hours
        FROM #{ScheduleEntry.table_name}
        WHERE user_id = #{User.current.id}
            AND date BETWEEN '%s' AND '%s'
        UNION
        SELECT project_id, spent_on AS date, null AS hours, sum(#{TimeEntry.table_name}.hours) AS logged_hours
        FROM #{TimeEntry.table_name}
        WHERE user_id = #{User.current.id}
            AND spent_on BETWEEN '%s' AND '%s'
        GROUP BY project_id, date
    ) AS results
    LEFT JOIN #{Project.table_name} AS p ON p.id = results.project_id
    GROUP BY project_id

- Sammy

---

你希望那个查询返回什么？ - Anon.

Answer 1

将GROUP BY project_id改为GROUP BY p.name,p.id

文档指出：

当存在GROUP BY时，除了在聚合函数中引用未分组列外，在SELECT列表达式中引用未分组列是无效的，因为对于未分组列会有多个可能的值可返回。

- Stephen Denne

---

但是它会给我相同的结果吗？ - Sammy

它不会给出相同的结果。它将按p,name分组，然后按p.id分组，而不是按project_id分组。结果不同。 - Artem Kalinchuk