How to Fix the \'Expression of SELECT List Not in GROUP BY Clause\' MySQL Error?

Troubleshooting "Expression of SELECT List Not in GROUP BY Clause" Error

The error, "Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column 'libelle'," is typically encountered when executing a query with a non-aggregated column in the SELECT list and GROUP BY clause. To resolve this error, it is necessary to ensure that all non-aggregated columns are included in the GROUP BY clause or are aggregated using functions such as SUM(), AVG(), or COUNT().

In this specific case, the query attempts to select columns including 'libelle', 'credit_initial', 'disponible_v', and 'montant'. However, only 'libelle' is included in the GROUP BY clause, while 'montant' is non-aggregated. To fix this issue, modify the query to either include 'montant' in the GROUP BY clause or aggregate it using SUM() or another appropriate function.

SOLUTION

Here is an updated version of the query that includes 'montant' in the GROUP BY clause:

SELECT libelle, credit_initial, disponible_v, SUM(montant) AS total_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

Alternately, the query can be adjusted to aggregate 'montant' using SUM():

SELECT libelle, credit_initial, disponible_v, SUM(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

By making these changes, the query will conform to MySQL 5.7's strict GROUP BY mode and avoid the error.

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