Why Does MySQL Throw a \'Expression of SELECT List Not in GROUP BY Clause\' Error, and How Can I Fix It?

How to Resolve the "Expression of SELECT List Not in GROUP BY Clause" Error in MySQL

In MySQL, executing queries can sometimes result in errors related to the sql_mode setting. One such error is:

#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

This error occurs when a query includes a column in the SELECT list that is not included in the GROUP BY clause and is not aggregated (e.g., using SUM, COUNT, etc.). To resolve this issue, the query must be modified to include the non-aggregated column in the GROUP BY clause or to aggregate it.

In the provided MySQL query:

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
order by libelle asc

The non-aggregated column is disponible_v. To resolve the error, add it to the GROUP BY clause:

...
GROUP BY libelle, disponible_v
...

Alternatively, aggregate disponible_v using a function such as SUM or AVG:

...
GROUP BY libelle
HAVING SUM(disponible_v) > 0
...

For MySQL versions prior to 8.0, you may need to adjust the sql_mode setting in the MySQL configuration file /etc/mysql/conf.d/mysql.cnf by adding 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

Restart MySQL after making the changes to the configuration file.

The above is the detailed content of Why Does MySQL Throw a \'Expression of SELECT List Not in GROUP BY Clause\' Error, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!