Expression not in GROUP BY key

在你的查询语句中，你使用了聚合函数 `SUM` 来计算 `assign_amt_sum` 和 `nh_assign_amt_sum` 字段的总和。然而，在 SQL 中，如果你同时使用了聚合函数和非聚合字段，那么非聚合字段必须在 `GROUP BY` 子句中出现。

根据你的查询需求，如果你想要保留每个门店的详细信息，并在最后进行总和计算，你可以使用子查询来实现。以下是修正后的查询语句：

SELECT 
    city_table.performance_city_abbr AS `业城`,
    city_table.is_core AS `重点城市`,
    city_table.city_level AS `城市等级`,
    m_table.shop_code AS `门店编码`,
    m_table.shop_grade AS `门店等级`,
    m_table.amt_time AS `月份`,
    m_table.assign_amt_sum AS `二手+租赁业绩`,
    m_table.nh_assign_amt_sum AS `新房业绩`
FROM (
    SELECT 
        a.pt, 
        a.city_name, 
        a.brand_name, 
        a.shop_code, 
        a.shop_grade,
        b.shop_code,
        b.assign_amt_sum,
        b.nh_assign_amt_sum,
        substr(b.pt, 1, 6) AS amt_time
    FROM rpt.rpt_shh_beikefen_agent_shop_ma b
    LEFT JOIN rpt.rpt_display_shop_grade_allinfo_ma a
    ON b.shop_code = a.shop_code
        AND substr(b.pt, 1, 6) = substr(a.pt, 1, 6)
    WHERE b.pt BETWEEN '20230630000000' AND '20230630000000'
) m_table
JOIN (
    SELECT 
        city_code, 
        city_name, 
        performance_city_abbr, 
        is_agent_city, 
        bkhealth,
        city_level,
        IF(is_core_30_city_yj = 1, '重点30城', '非重点') AS is_core
    FROM dim.dim_grm_city_region_mapping_da
    WHERE pt = "20230718000000" 
    AND bkhealth = 1
) city_table
ON m_table.city_name = city_table.city_name
WHERE m_table.amt_time BETWEEN "202206" AND "202306" 
AND m_table.shop_grade = 'A';

修正后的查询语句中，我移除了 `SUM` 函数，并将字段直接选择到结果集中。这样，你将得到每个门店的详细信息，而不是对这些字段进行总和计算。