Software⏱️ 6 min read📅 2026-06-03

How to Fix: Error related to only_full_group_by when executing a query in MySql

Error in MySql query due to sql_mode=only_full_group_by

Quick Answer: Change the sql_mode to 'STRICT_ALL_TABLES' or 'STRICT_ALL_TABLES ENFORCE' to resolve the issue

📋 Table of Contents

  1. ⚠️ Common Causes
  2. 🚀 How to Resolve This Issue
  3. ✨ Wrapping Up

Error related to only_full_group_by is an error that occurs when you're trying to execute a query in MySQL where you have non-aggregated columns in your SELECT list that are not functionally dependent on columns in the GROUP BY clause. This error typically affects users who have upgraded their system and installed MySQL 5.7.9 with PHP for a web application. The error is frustrating because it prevents the query from executing, causing downtime for the application. In this guide, we'll walk you through the steps to resolve this issue.

The only_full_group_by error can be particularly frustrating when you're trying to troubleshoot a complex SQL query. However, by following these steps, you should be able to identify and fix the issue, ensuring that your web application continues to run smoothly.

⚠️ Common Causes

  • The primary reason for this error is the introduction of MySQL 5.7.9's only_full_group_by mode. In this mode, MySQL requires all non-aggregated columns in the SELECT list to be included in the GROUP BY clause. This means that if you have a query with non-aggregated columns that are not functionally dependent on the columns in the GROUP BY clause, it will throw an error. An alternative reason for this error could be a misconfigured or missing GROUP BY clause in your SQL query.
  • Another potential cause of this error is when you're using a MySQL version older than 5.7.8 and upgrade to 5.7.9 without making the necessary changes to your queries. In this case, you'll need to update your queries to include all non-aggregated columns in the GROUP BY clause.
  • In some cases, the error could also be caused by a misconfigured or missing index on one of the tables involved in the query. This can lead to MySQL not being able to optimize the query correctly, resulting in the only_full_group_by error.

🚀 How to Resolve This Issue

Update your SQL queries to include all non-aggregated columns in the GROUP BY clause

  1. Step 1: Check your SQL queries for any non-aggregated columns that are not included in the GROUP BY clause. If you find such a column, update your query to include it in the GROUP BY clause.
  2. Step 2: If the column is aggregated (e.g., SUM, COUNT, MAX, MIN), make sure it's being grouped correctly. You can do this by checking the aggregate function used and ensuring that it's applied to all rows in the group.
  3. Step 3: In some cases, you may need to use a subquery or join to include the non-aggregated column in the GROUP BY clause. Be cautious when using these approaches, as they can lead to performance issues if not done correctly.
  4. Step 4: Test your updated query to ensure it executes successfully and produces the correct results.
  5. Step 5: If you're still experiencing issues after updating your queries, try running the MySQL query with the --skip-outerjoin option to see if the error persists. If it does, then the issue is likely due to a misconfigured or missing index on one of the tables involved in the query.
  6. Step 6: In this case, you'll need to update your database schema to include the necessary indexes and re-run the queries with the updated indexes in place.

Use a subquery or join to include non-aggregated columns in the GROUP BY clause

  1. Step 1: If you can't update your SQL queries to include all non-aggregated columns in the GROUP BY clause, consider using a subquery or join to include them. This approach requires careful planning and execution to avoid performance issues.
  2. Step 2: Use a subquery to include the non-aggregated column in the GROUP BY clause. For example: SELECT * FROM (SELECT group_id, SUM(value) AS total_value FROM table GROUP BY group_id) AS subquery JOIN main_table ON main_table.group_id = subquery.group_id
  3. Step 3: Alternatively, you can use a join to include the non-aggregated column in the GROUP BY clause. For example: SELECT * FROM main_table JOIN (SELECT group_id, SUM(value) AS total_value FROM table GROUP BY group_id) AS subquery ON main_table.group_id = subquery.group_id
  4. Step 4: Be cautious when using these approaches, as they can lead to performance issues if not done correctly.
  5. Step 5: Test your updated query to ensure it executes successfully and produces the correct results.
  6. Step 6: If you're still experiencing issues after updating your queries or using a subquery or join, try running the MySQL query with the --skip-outerjoin option to see if the error persists. If it does, then the issue is likely due to a misconfigured or missing index on one of the tables involved in the query.

✨ Wrapping Up

By following these steps and understanding the root causes of the only_full_group_by error, you should be able to resolve this issue and ensure that your web application continues to run smoothly. Remember to test your queries thoroughly after making any changes to avoid any unexpected behavior or performance issues.

Did this fix your problem?

If not, try searching for specific error codes.

🔍 Search Error Database

❓ Frequently Asked Questions

🛠️ Related Fixes

How to Fix: Pc crashes shortly after launching game (rainbow

Fix Pc crashes shortly after launching game (rainbow six siege). Compl

How to Fix: Installing an APK on a locked down phone

Installing an APK on a locked down phone: Try using a rooted device, e

How to Fix: New PC build- no signal and no clue

Fix New PC build- no signal and no clue. Complete troubleshooting guid