How to Fix MySQL's 'SELECT List Incompatible with GROUP BY' Error?

Resolving "SELECT List Incompatible with GROUP BY" Error with SQL_MODE

MySQL's error message:

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

indicates that the SELECT list contains non-aggregated columns that are not in the GROUP BY clause. This error occurs when using MySQL's only_full_group_by SQL mode.

Potential Solutions:

• Change SQL Mode:

You can temporarily disable the only_full_group_by mode by executing the following command:

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

• Modify Query:

Alternatively, you can modify your query to include all columns from the SELECT list in the GROUP BY clause:

SELECT *
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND
      `status` = 'Active'
GROUP BY `load_id`, `bill_id`, `latitude`, `langitude`, `proof_type`, `document_type`, `file_name`, `is_private`, `status`, `createdon`, `updatedon`

• Use Aggregator Functions:

For best practices in Structured Query Language, avoid selecting all columns. Instead, use aggregator functions on the grouping columns, such as:

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

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