ERROR 1055 occurs when a SELECT statement includes non-aggregated columns not in the GROUP BY clause while ONLY_FULL_GROUP_BY mode is enabled. Fix by adding missing columns to GROUP BY, using aggregate functions, or applying ANY_VALUE() to accept arbitrary values.
Error 1055 is enforced by MySQL's ONLY_FULL_GROUP_BY SQL mode (enabled by default in MySQL 5.7.5+). It prevents ambiguous GROUP BY queries by requiring that all non-aggregated columns in the SELECT list either appear in the GROUP BY clause or are aggregate functions. Without this restriction, different database implementations would return non-deterministic results, with arbitrary values picked for columns not in the GROUP BY clause. This error typically occurs when you want to retrieve grouped data but accidentally select columns that MySQL cannot determine how to group correctly. For example, if you group by customer_id but select customer_name without aggregating it, MySQL cannot guarantee which customer_name appears in the result if one customer has multiple names in your data.
The error message indicates which expression is problematic. For example:
SELECT customer_id, customer_name, COUNT(*)
FROM orders
GROUP BY customer_id;MySQL will report that customer_name is not in GROUP BY. Look at the column positions or names in your SELECT clause.
Include every column in the SELECT list that isn't wrapped in an aggregate function (SUM, COUNT, MAX, AVG, etc.) into the GROUP BY clause:
-- Before (ERROR)
SELECT customer_id, customer_name, COUNT(*)
FROM orders
GROUP BY customer_id;
-- After (FIXED)
SELECT customer_id, customer_name, COUNT(*)
FROM orders
GROUP BY customer_id, customer_name;This is the most correct solution as it makes your query intent explicit and guarantees deterministic results.
If you need a column that's not grouped, wrap it in an aggregate function like MAX(), MIN(), GROUP_CONCAT(), or ANY_VALUE():
-- Using MAX to get one value per group
SELECT customer_id, MAX(customer_name), COUNT(*)
FROM orders
GROUP BY customer_id;
-- Using GROUP_CONCAT to see all values
SELECT customer_id, GROUP_CONCAT(customer_name), COUNT(*)
FROM orders
GROUP BY customer_id;Choose the function that makes sense for your data. MAX() works if you just need any value; GROUP_CONCAT() works if you need to see all values concatenated.
If you know a column is functionally dependent on your GROUP BY columns (e.g., email uniquely identifies a customer), use ANY_VALUE() to tell MySQL you accept an arbitrary value:
SELECT customer_id, ANY_VALUE(email), COUNT(*)
FROM orders
GROUP BY customer_id;ANY_VALUE() explicitly documents that you understand the risks and are intentionally picking one arbitrary value per group.
Verify which SQL mode is active:
-- Check session SQL mode
SELECT @@sql_mode;
-- Check global SQL mode
SELECT @@GLOBAL.sql_mode;You should see ONLY_FULL_GROUP_BY in the output if this mode is enabled. Understanding your SQL mode helps identify if this was a recent change after upgrading MySQL.
If you need a quick fix for a single session (not recommended for production):
-- Temporarily disable for current session only
SET sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
-- Then run your query
SELECT customer_id, customer_name, COUNT(*)
FROM orders
GROUP BY customer_id;This is a temporary workaround and does NOT solve the underlying query logic issue. Results may become non-deterministic.
To disable globally, edit your MySQL configuration file (my.cnf or my.ini):
[mysqld]
sql_mode = ""Then restart MySQL:
# Linux/Mac
sudo systemctl restart mysql
# Or
sudo /usr/local/mysql/support-files/mysql.server restartWARNING: Disabling this mode removes important validation and can lead to non-deterministic, unpredictable query results that vary based on storage engine or query optimizer changes.
Understanding Functional Dependency:
MySQL allows non-grouped columns if they are "functionally dependent" on grouped columns. For example, if PRIMARY KEY customer_id uniquely identifies each customer, then customer_name is functionally dependent on customer_id. However, MySQL only recognizes explicit PRIMARY or UNIQUE key relationships as functional dependencies, not implicit business logic.
Behavior in Different MySQL Versions:
- MySQL 5.6 and earlier: ONLY_FULL_GROUP_BY disabled by default; ambiguous queries allowed
- MySQL 5.7.5+: ONLY_FULL_GROUP_BY enabled by default; strict validation enforced
- MySQL 8.0+: Continues enforcing ONLY_FULL_GROUP_BY by default
Migration Path for Legacy Applications:
If you're upgrading old code, prioritize fixing queries rather than disabling the mode. Use ANY_VALUE() for intentional non-grouped columns and add missing columns to GROUP BY for others. This ensures your code remains maintainable and produces deterministic results.
Window Functions Alternative (MySQL 8.0+):
For complex grouping scenarios, consider window functions as a more modern alternative to GROUP BY:
SELECT
customer_id,
customer_name,
ROW_NUMBER() OVER (ORDER BY order_date DESC) as order_rank
FROM orders;ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions