mysql - #1055 - SELECT 列表的表达式 #2 不在 GROUP BY 子句中并且包含非聚合列 'osunemonitor.r.entry_date'

Question

我正在尝试按 ward_name 进行分组，这是我唯一需要分组的内容，但我收到此错误:

\ #1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'osunemonitor.r.entry_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

在我的查询中，如何使其仅按 ward_name 分组？

我的查询:

SELECT 
    pu.pu_ward_name, 
    entry_date,
    pu.o_code,
    pu.pu_ward_name,
    pu.pu_lga_name,
    pu.pu_code,
    pu.pu_ward_code,
    pu.pu_lga_code,
    pu.pu_voters_reg_count, 
    SUM(total_vote_cast) AS total_vote_cast,
    SUM(apc_total_vote) AS apc_total_vote,
    SUM(pdp_total_vote) AS pdp_total_vote,
    SUM(adp) AS adp, ward_name
FROM 
    post_election_info r 
    LEFT JOIN polling_unit pu ON r.ward_name = pu.pu_ward_name 
WHERE lga_name = 'BORIPE'
GROUP BY 
    pu.pu_ward_name

Answer 1

每个非聚合列都必须出现在GROUP BY 子句中。这是 ANSI SQL 中的一个标准，尽管古代版本的 MySQL 曾经对此很宽松。

想一想:如果您SELECT一列，但没有GROUP BY它，RDBMS 如何可靠地选择应显示组中的哪个值？

可能的解决方案包括:

• 将所有其他非聚合列添加到 GROUP BY 子句
• 对除 pu_ward_name 之外的所有列使用聚合函数。例如，MAX() 和 MIN() 是字符串友好的(与 SUM() 不同)。

第一个解决方案:

SELECT 
    pu.pu_ward_name, 
    entry_date,
    pu.o_code,
    pu.pu_lga_name,
    pu.pu_code,
    pu.pu_ward_code,
    pu.pu_lga_code,
    pu.pu_voters_reg_count, 
    SUM(total_vote_cast) AS total_vote_cast,
    SUM(apc_total_vote)  AS apc_total_vote,
    SUM(pdp_total_vote)  AS pdp_total_vote,
    SUM(adp) AS adp
FROM 
    post_election_info r 
    LEFT JOIN polling_unit pu ON r.ward_name = pu.pu_ward_name 
WHERE lga_name = 'BORIPE'
GROUP BY 
    pu.pu_ward_name, 
    entry_date,
    pu.o_code,
    pu.pu_lga_name,
    pu.pu_code,
    pu.pu_ward_code,
    pu.pu_lga_code,
    pu.pu_voters_reg_count

第二个解决方案:

SELECT 
    pu.pu_ward_name, 
    MAX(entry_date)             AS entry_date,
    MAX(pu.o_code)              AS o_code,
    MAX(pu.pu_lga_name)         AS pu_lga_name,
    MAX(pu.pu_code)             AS pu_code,
    MAX(pu.pu_ward_code)        AS pu_ward_code,
    MAX(pu.pu_lga_code)         AS pu_lga_code,
    MAX(pu.pu_voters_reg_count) AS pu_voters_reg_count, 
    SUM(total_vote_cast)        AS total_vote_cast,
    SUM(apc_total_vote)         AS apc_total_vote,
    SUM(pdp_total_vote)         AS pdp_total_vote,
    SUM(adp)                    AS adp
FROM 
    post_election_info r 
    LEFT JOIN polling_unit pu ON r.ward_name = pu.pu_ward_name 
WHERE lga_name = 'BORIPE'
GROUP BY 
    pu.pu_ward_name