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

Question

My SQL statement returns properly on my laptop's MySQL (Server version: 5.5.62-0ubuntu0.14.04.1 - (Ubuntu)) but on my server (Server version: 5.7.26-0ubuntu0.16.04.1 - (Ubuntu)) it returns an error.

SELECT * 
FROM `orders` 
WHERE `mail_sent`='No' 
  AND `datetime` < DATE_SUB(NOW(), INTERVAL 15 MINUTE) 
GROUP BY `contact_id` 
ORDER BY `datetime` ASC;

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.orders.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Answer 1

I think this problem due to strict mode enabled in your MySQL version. Kindly disable strict mode and try again.

To disable follow How to turn on/off MySQL strict mode in localhost (xampp)?

STRICT_TRANS_TABLES is responsible for setting MySQL strict mode.

To check whether strict mode is enabled or not run the below sql:

SHOW VARIABLES LIKE 'sql_mode';

If one of the value is STRICT_TRANS_TABLES, then strict mode is enabled, else not. In my case it gave

+--------------+------------------------------------------+ 
|Variable_name |Value                                     |
+--------------+------------------------------------------+
|sql_mode      |STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION|
+--------------+------------------------------------------+

Hence strict mode is enabled in my case as one of the value is STRICT_TRANS_TABLES.

To disable strict mode run the below sql:

set global sql_mode='';

[or any mode except STRICT_TRANS_TABLES. Ex: set global sql_mode='NO_ENGINE_SUBSTITUTION';]

To again enable strict mode run the below sql:

set global sql_mode='STRICT_TRANS_TABLES';

If you don't what to disable strict_mode then you have to change your sql query to follow sql standard.

like this..

SELECT
   aggregated_column,
   aggregated_column,
   aggregation_function(column),
   aggregation_function(column),
   aggregation_function(column)
FROM
  TABLE
WHERE [CLAUSE]
GROUP BY [GROUP BY FIELD]
WHILE [CLAUSE]
ORDER BY 

for your query..

SELECT 
  contact_id,
  min(mail_sent),
  min(datetime),
  max(datetime),
FROM `orders` 
WHERE `mail_sent`='No' 
  AND `datetime` < DATE_SUB(NOW(), INTERVAL 15 MINUTE) 
GROUP BY `contact_id` 
ORDER BY `datetime` ASC;

Let me know if that helps.

Answer 2

Another quick fix is to use ANY_VALUE(contact_id) as contact_id instead of only contact_id in your SELECT.

For example:

SELECT ANY_VALUE(contact_id) as contact_id, ANY_VALUE(mail_sent) as mail_sent, ANY_VALUE(datetime) AS datetime
    WHERE ...
    GROUP BY contact_id
    ORDER BY datetime DESC 

Note that the field you group by (e.g. contact_id) must be selected with ANY_VALUE(contact_id) AS contact_id.

You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column.

Worth to read: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Answer 3

Put the remainder of the columns (except contact_id) inside aggregate functions. In my limited experience, MySQL isn't completely consistent in how it handles non-grouped columns outside of aggregate functions. Best case, it's not deterministic.