Troubleshooting "SELECT list is not in GROUP BY clause" Error with MySQL

The error "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column" typically occurs in MySQL when the result of a query contains non-aggregated columns that are not included in the GROUP BY clause. This is due to the sql_mode=only_full_group_by setting, which enforces stricter grouping rules.

To address this issue, there are several solutions:

1. Disable sql_mode=only_full_group_by: This can be done with the following command:

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

2. Include all selected columns in the GROUP BY clause: This ensures that all columns in the SELECT list are included in the GROUP BY operation. For example:

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

3. Use aggregation functions: Instead of selecting individual columns, use aggregation functions such as SUM(), COUNT(), or AVG() to group the data. This eliminates the need for the GROUP BY clause. For example:

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

It is important to note that changing the SQL mode is not the best practice. The preferred solution is to modify the query to comply with the stricter grouping rules.