pgsql报错 must appear in the GROUP BY clause or be used in an aggregate function

小咪咪 2022-12-25 14:59 208阅读 0赞

# **[must appear in the GROUP BY clause or be used in an aggregate function][]** #

今天在分组统计的时候pgsql报错 `must appear in the GROUP BY clause or be used in an aggregate function`，在mysql里面是可以的，但是pgsql报错，这是pgsql一个常见的聚合问题，在SQL3标准以前，选择显示的字段必须出现在在 `GROUP BY` 中。下面我把问题描述一下：

数据是这样的

![20201202154846112.png][]

我想sum(avg)根据cname 进行计算期望结果为

![20201202154938643.png][]

具体pgsql如下：

SELECT  
    T .cname,  
    M .wmname,  
    T . MAX  
FROM  
    (  
        SELECT  
            cname,  
            MAX (AVG) AS MAX  
        FROM  
            makerar  
        GROUP BY  
            cname  
    ) T  
LEFT JOIN makerar M ON T .cname = M .cname  ;

参考：[https://www.cnblogs.com/zxhyJack/p/9934259.html][must appear in the GROUP BY clause or be used in an aggregate function]

## ##

[must appear in the GROUP BY clause or be used in an aggregate function]: https://www.cnblogs.com/zxhyJack/p/9934259.html
[20201202154846112.png]: /images/20221120/84eb3d271f5b47969438a76944a699d9.png
[20201202154938643.png]: /images/20221120/475c431403bb471c89886a269fe3ae78.png