SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

👋 Hey there! Are you struggling with the "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column" error in MySQL? We've got you covered! 😎

📝 In this blog post, we will address this common issue and provide you with easy solutions to fix it. So, let's jump right into it! 🚀

💡 The error you encountered usually occurs when you have the sql_mode set to only_full_group_by in your MySQL configuration. This mode requires that all columns in the SELECT list, other than the ones being aggregated (e.g., using functions like COUNT, SUM, etc.), are included in the GROUP BY clause.

🔎 In your specific query, you're selecting all columns (*) from the tbl_customer_pod_uploads table, grouping them by proof_type. However, the error is thrown because some columns in your SELECT list (e.g., id) are not included in the GROUP BY clause, and they are not functionally dependent on the grouped column.

🛠️ To resolve this issue, you have a few options:

1️⃣ Include all non-aggregated columns in the GROUP BY clause:

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

2️⃣ Use aggregate functions for non-grouped columns:

SELECT MAX(`id`) AS `id`, MAX(`user_id`) AS `user_id`, MAX(`load_id`) AS `load_id`, MAX(`bill_id`) AS `bill_id`, `proof_type` -- and so on
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND `status` = 'Active'
GROUP BY `proof_type`

3️⃣ Disable only_full_group_by mode (not recommended): Depending on your environment and requirements, you can disable the only_full_group_by mode in your MySQL configuration. However, this is not recommended as it can hide potential errors in your queries.

⚡️ With these solutions, you should now be able to run your query without encountering the error.

📢 If you're looking for even more in-depth explanations and examples, be sure to check out our blog post on this topic. And hey, if you found this information helpful, don't forget to share it with your fellow developers! Sharing is caring, remember? 🤗

💬 We would love to hear about your experience with this error and if our solutions worked for you! Leave us a comment below or reach out to us on social media.

Happy coding! 💻🎉