PGError：错误：列“ p.name”必须出现在GROUP BY子句中或在聚合函数中使用 [英] PGError: ERROR: column "p.name" must appear in the GROUP BY clause or be used in an aggregate function

问题描述

此查询出现错误。为什么？我不明白：（

I get an error with this query. Why? I don't understand :(

    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

推荐答案

将 GROUP BY project_id 更改为 GROUP BY p.name，p.id

文档说：

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

When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column.

这篇关于PGError：错误：列“ p.name”必须出现在GROUP BY子句中或在聚合函数中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！