How to Fix the MySQL \'Expression of SELECT list is not in GROUP BY clause\' Error?

Troubleshooting the "Expression of SELECT list is not in GROUP BY clause" Error

Problem:

When executing a query that includes non-aggregated columns in the GROUP BY clause, you encounter the following error:

#1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by

Reason:

This error occurs when you have non-aggregated columns in the SELECT list that are not included in the GROUP BY clause. In MySQL version 5.7.12, the only_full_group_by SQL mode is enabled by default, which enforces stricter grouping rules.

Solution:

To resolve this error, you can either:

1. Add the non-aggregated columns to the GROUP BY clause:

This ensures that the query only returns rows that have the same values for all columns in the GROUP BY clause.

SELECT libelle, credit_initial, disponible_v, SUM(montant) AS montant 
FROM fiche, annee, type 
WHERE type.id_type = annee.id_type AND annee.id_annee = fiche.id_annee 
  AND annee = YEAR(CURRENT_TIMESTAMP)
GROUP BY libelle, credit_initial, disponible_v
ORDER BY libelle ASC;

2. Disable the only_full_group_by SQL mode:

This allows the query to return rows that may not have the same values for all columns in the GROUP BY clause.

sudo vim /etc/mysql/conf.d/mysql.cnf

Scroll to the bottom of the file and add the following lines:

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Save and exit vim. Restart MySQL:

sudo service mysql restart

After restarting MySQL, the query should execute successfully without the error.

The above is the detailed content of How to Fix the MySQL \'Expression of SELECT list is not in GROUP BY clause\' Error?. For more information, please follow other related articles on the PHP Chinese website!