How to Resolve MySQL Error: SELECT List Incompatible with SQL_MODE=ONLY_FULL_GROUP_BY?

MySQL Error: SELECT List Incompatible with SQL_MODE=ONLY_FULL_GROUP_BY

When executing a query that includes a SELECT statement with non-aggregated columns in the grouping clause, MySQL may return an error indicating that the expression is not in the GROUP BY clause. This can occur when SQL_MODE is set to ONLY_FULL_GROUP_BY, which requires that all non-aggregated columns in the SELECT list be included in the GROUP BY clause.

Solution 1: Disable SQL_MODE=ONLY_FULL_GROUP_BY

One solution is to disable the SQL_MODE=ONLY_FULL_GROUP_BY setting by issuing the following command:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

This will allow the query to execute without the error.

Solution 2: Include All Non-Aggregated Columns in GROUP BY Clause

Another solution is to include all non-aggregated columns in the GROUP BY clause. This can be done by modifying the query to include the following:

SELECT *
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND
      `status` = 'Active'
GROUP BY `proof_type`, `id`

This will ensure that all non-aggregated columns are included in the GROUP BY clause and the query will execute without the error.

Solution 3: Use Aggregator Functions

For best practice, consider using aggregator functions for the non-aggregated columns instead of selecting all columns. For example, if you only need the count of records for each proof type, the query can be modified as follows:

SELECT `proof_type`, COUNT(*) AS `total_records`
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND
      `status` = 'Active'
GROUP BY `proof_type`

The above is the detailed content of How to Resolve MySQL Error: SELECT List Incompatible with SQL_MODE=ONLY_FULL_GROUP_BY?. For more information, please follow other related articles on the PHP Chinese website!