#1055 - Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Question

I have a problem with the query below in MySQL 5.7, but in MySQL 5.6 it is working well.

This message appears every time:

1055 - Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column
'electricity_databases.electricity_invoices.date_inserted' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SQL code:

SELECT 
homes.id,
homes.homeName,
homes.city, 
homes.date_registered,
ROUND(SUM(electricity_invoices.total), 2) AS TotalPrice,
DATEDIFF(NOW(), electricity_invoices.date_inserted) AS last_insert_in_days,
MAX(electricity_invoices.date_inserted) AS last_insert,
COUNT(electricity_invoices.homeID) AS countPaymentTimes,
MAX(electricity_invoices.currRead) AS currRead,
MAX(electricity_invoices.prevRead) AS prevRead,
ROUND(MAX(electricity_invoices.currRead) - MAX(electricity_invoices.prevRead), 1) AS lastComp,
customer.name

FROM homes

LEFT JOIN electricity_invoices ON
homes.id = electricity_invoices.homeID

LEFT JOIN customer ON
homes.id = customer.homeID

GROUP BY homes.id
ORDER BY homes.id

Gurwinder Singh · Answer 1 · 2017-03-08T18:52:20.920

Reason is that in latest versions of MySQL, be default it is not allowed to not add non aggregated column in the group by clause. You can disable this behavior by disable sql_mode from full group by mode.

Just add the non-aggregated columns in the group by clause.

select homes.id,
    homes.homeName,
    homes.city,
    homes.date_registered,
    ROUND(SUM(electricity_invoices.total), 2) as TotalPrice,
    DATEDIFF(NOW(), electricity_invoices.date_inserted) as last_insert_in_days,
    MAX(electricity_invoices.date_inserted) as last_insert,
    COUNT(electricity_invoices.homeID) as countPaymentTimes,
    MAX(electricity_invoices.currRead) as currRead,
    MAX(electricity_invoices.prevRead) as prevRead,
    ROUND(MAX(electricity_invoices.currRead) - MAX(electricity_invoices.prevRead), 1) as lastComp,
    customer.name
from homes
left join electricity_invoices on homes.id = electricity_invoices.homeID
left join customer on homes.id = customer.homeID
group by homes.id,
    homes.homeName,
    homes.city,
    homes.date_registered,
    customer.name

@mohdadawe - Take a look at http://stackoverflow.com/questions/23921117/disable-only-full-group-by — Gurwinder Singh, Mar 09 '17 at 04:22

score 0 · Answer 2 · answered Mar 08 '17 at 18:54

starting to mysql 5.7 you can select non aggregated columns not present in group if you want the same behavior of previous versions you must revoke sql_mode=only_full_group_by (using SET sql_mode = '') or more properly you should buil a the select or assing to group by all the column not aggregated eg:

SELECT 
homes.id,
homes.homeName,
homes.city, 
homes.date_registered,
ROUND(SUM(electricity_invoices.total), 2) AS TotalPrice,
DATEDIFF(NOW(), electricity_invoices.date_inserted) AS last_insert_in_days,
MAX(electricity_invoices.date_inserted) AS last_insert,
COUNT(electricity_invoices.homeID) AS countPaymentTimes,
MAX(electricity_invoices.currRead) AS currRead,
MAX(electricity_invoices.prevRead) AS prevRead,
ROUND(MAX(electricity_invoices.currRead) - MAX(electricity_invoices.prevRead), 1) AS lastComp,
customer.name

FROM homes

LEFT JOIN electricity_invoices ON
homes.id = electricity_invoices.homeID

LEFT JOIN customer ON
homes.id = customer.homeID

GROUP BY homes.id, homes.homeName,homes.city, homes.date_registered, DATEDIFF(NOW(), electricity_invoices.date_inserted) AS last_insert_in_days
ORDER BY homes.id

or , due the fact that you don't need specific value for these columns , use (fake) aggregation for these column

SELECT 
homes.id,
min(homes.homeName),
min(homes.city), 
min(homes.date_registered),
ROUND(SUM(electricity_invoices.total), 2) AS TotalPrice,
min(DATEDIFF(NOW(), electricity_invoices.date_inserted) AS last_insert_in_days),
MAX(electricity_invoices.date_inserted) AS last_insert,
COUNT(electricity_invoices.homeID) AS countPaymentTimes,
MAX(electricity_invoices.currRead) AS currRead,
MAX(electricity_invoices.prevRead) AS prevRead,
ROUND(MAX(electricity_invoices.currRead) - MAX(electricity_invoices.prevRead), 1) AS lastComp,
customer.name

FROM homes

LEFT JOIN electricity_invoices ON
homes.id = electricity_invoices.homeID

LEFT JOIN customer ON
homes.id = customer.homeID

GROUP BY homes.id
ORDER BY homes.id

how to disable sql_mode, and if i disabled it can damage the database — mohdadawe, Mar 08 '17 at 19:09
No .. you don't damage the database ... only you use the same (old and unpredicatble )mode used by previous version.. But using the old mode you use group by in the wrong way .. — ScaisEdge, Mar 08 '17 at 19:10

#1055 - Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column

2 Answers2