#1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'osunemonitor.r.entry_date'

Question

I am trying to group by ward_name which is the only thing I need to group by but I am getting this error:

\ #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

In my query how can I make it group by only ward_name ?

My query:

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

Every non-aggregated column must appear in the GROUP BY clause. This is a standard in ANSI SQL, although ancient versions of MySQL used to be lax about it.

Think about it : if you SELECT a column but do not GROUP BY it, how would the RDBMS reliably choose which value in the group should be displayed ?

Possible solutions include :

• add all other non-aggregated columns to the GROUP BY clause
• use aggregate functions around all colmns excepted pu_ward_name. MAX() and MIN(), for example, are string-friendly (unlike SUM()).

First solution :

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

Second solution :

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