请教，帮我看看为什么sql在分组的时候会报错

问题遇到的现象和发生背景

我在查询客户公司的数据

问题相关代码，请勿粘贴截图

SELECT
            MAX( a.order_code ) AS orderCode,
            MAX( a.finishDate ) AS finishDate,
            MAX( c100.id ) AS "office.id",
            MAX( c100.name ) AS "office.name",
            MAX( sa.id ) AS "area.id",
            MAX( sa.name ) AS "area.name",
            MAX( cc.id ) AS "cpClient.id",
            MAX( cc.clientCpmpany ) AS "cpClient.clientCpmpany",
            MAX( a.order_type ) AS orderType,
            MAX( a.project ) AS project,
            MAX( a.settlement_type ) AS settlementType,
            MIN( a.TYPE ) AS atype,
            MAX( a.TYPE ) AS btype
            ,
            NVL(
                SUM(
                    CASE WHEN(MIN( a.TYPE ) !=3 OR MAX( a.TYPE ) !=3 AND to_char(finishDate,'yyyy-mm') = '2021-07') THEN total_money END
                ),0
            ) AS totalMoney
        FROM
            CP_CHAT_CAPACITY a
            JOIN sys_office c100 ON c100.id = a.OFFICE_ID
            JOIN sys_area sa ON sa.id = c100.area_id
            JOIN cp_client cc ON cc.id = a.client_id 
            AND a.del_flag = '0' 
            AND 2 = 2 
            AND '2021-07-01' <= to_char( a.finishDate, 'yyyy-mm-dd' ) 
            AND to_char( a.finishDate, 'yyyy-mm-dd' ) <= '2021-07-31' 
        GROUP BY
            order_code,
            finishDate

运行结果及报错内容

ORA-00979: not a GROUP BY expression

我的解答思路和尝试过的方法

我想要达到的结果

我想要能查询出来

语法问题，分组字段要取表名存在的字段，而不是查询结果的别名。

SELECT
            MAX( a.order_code ) AS orderCode,
            MAX( a.finishDate ) AS finishDate,
            MAX( c100.id ) AS "office.id",
            MAX( c100.name ) AS "office.name",
            MAX( sa.id ) AS "area.id",
            MAX( sa.name ) AS "area.name",
            MAX( cc.id ) AS "cpClient.id",
            MAX( cc.clientCpmpany ) AS "cpClient.clientCpmpany",
            MAX( a.order_type ) AS orderType,
            MAX( a.project ) AS project,
            MAX( a.settlement_type ) AS settlementType,
            MIN( a.TYPE ) AS atype,
            MAX( a.TYPE ) AS btype
            ,
            NVL(
                SUM(
                    CASE WHEN(MIN( a.TYPE ) !=3 OR MAX( a.TYPE ) !=3 AND to_char(finishDate,'yyyy-mm') = '2021-07') THEN total_money END
                ),0
            ) AS totalMoney
        FROM
            CP_CHAT_CAPACITY a
            JOIN sys_office c100 ON c100.id = a.OFFICE_ID
            JOIN sys_area sa ON sa.id = c100.area_id
            JOIN cp_client cc ON cc.id = a.client_id 
            AND a.del_flag = '0' 
            AND 2 = 2 
            AND '2021-07-01' <= to_char( a.finishDate, 'yyyy-mm-dd' ) 
            AND to_char( a.finishDate, 'yyyy-mm-dd' ) <= '2021-07-31' 
        GROUP BY
            a.order_code,
            a.finishDate

或者作为子查询，套一层查询。

select * from (
  SELECT
            MAX( a.order_code ) AS orderCode,
            MAX( a.finishDate ) AS finishDate,
            MAX( c100.id ) AS "office.id",
            MAX( c100.name ) AS "office.name",
            MAX( sa.id ) AS "area.id",
            MAX( sa.name ) AS "area.name",
            MAX( cc.id ) AS "cpClient.id",
            MAX( cc.clientCpmpany ) AS "cpClient.clientCpmpany",
            MAX( a.order_type ) AS orderType,
            MAX( a.project ) AS project,
            MAX( a.settlement_type ) AS settlementType,
            MIN( a.TYPE ) AS atype,
            MAX( a.TYPE ) AS btype
            ,
            NVL(
                SUM(
                    CASE WHEN(MIN( a.TYPE ) !=3 OR MAX( a.TYPE ) !=3 AND to_char(finishDate,'yyyy-mm') = '2021-07') THEN total_money END
                ),0
            ) AS totalMoney
        FROM
            CP_CHAT_CAPACITY a
            JOIN sys_office c100 ON c100.id = a.OFFICE_ID
            JOIN sys_area sa ON sa.id = c100.area_id
            JOIN cp_client cc ON cc.id = a.client_id 
            AND a.del_flag = '0' 
            AND 2 = 2 
            AND '2021-07-01' <= to_char( a.finishDate, 'yyyy-mm-dd' ) 
            AND to_char( a.finishDate, 'yyyy-mm-dd' ) <= '2021-07-31' 
)
 GROUP BY
            orderCode,
            finishDate

你先把totalmoney这一个列注释掉试试,
因为你这个里面使用了嵌套聚合,即聚合函数里套聚合函数,那么在两个聚合函数之间,如果有使用非聚合字段,比如你这个sql里的"total_money",则会提示不是group by 表达式 。你试试看把total_money也加到group by 后面,它就不会报错了,但是明显这不是你想要的结果。
所以,你要么使用开窗函数去取MIN( a.TYPE )及MAX(a.TYPE),要么就嵌套一个子查询,反正别聚合函数套聚合函数

用这两个分组试试