How to fix ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 23, 2025· 229 views

This error occurs when you mix aggregate functions (MIN, MAX, COUNT) with non-aggregated columns in SELECT without a GROUP BY clause. Fix it by adding GROUP BY for all non-aggregated columns, using ANY_VALUE(), or ensuring functional dependency on a primary key.

Quick fix

Fixes 1140

-- WRONG: Mixing aggregate (COUNT) with non-aggregate (name)
SELECT name, COUNT(*) FROM users;

-- This fails because MySQL cannot determine which user name to return
  1. Identify the problematic columns in your query
  2. Add GROUP BY for all non-aggregated columns
  3. Use ANY_VALUE() for columns where the specific value is irrelevant
  4. Leverage functional dependency on primary keys
mysql — error
-- WRONG: Mixing aggregate (COUNT) with non-aggregate (name)
SELECT name, COUNT(*) FROM users;
-- This fails because MySQL cannot determine which user name to return
ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

What this error means

MySQL Error 1140 is raised by the strict ONLY_FULL_GROUP_BY SQL mode (enabled by default since MySQL 5.7) to prevent queries that would return non-deterministic results. When you use an aggregate function like COUNT(), SUM(), MIN(), or MAX() in your SELECT list but also select non-aggregated columns, MySQL cannot determine which single value to return for those non-aggregated columns when multiple rows are involved. The error prevents ambiguous queries that could produce unpredictable or incorrect results.

Typical symptoms

  • SELECT queries fail immediately with error message when using aggregate functions
  • Error appears even though you may only be selecting aggregated columns and table columns
  • Error message displays the expression number and column name causing the conflict
  • Queries work in some MySQL versions (older ones with ONLY_FULL_GROUP_BY disabled) but fail in newer versions

Common causes

  1. SELECT list contains both aggregate functions (COUNT, MIN, MAX, SUM, AVG) and non-aggregated columns without GROUP BY
  2. Non-aggregated columns in SELECT are not included in the GROUP BY clause
  3. ONLY_FULL_GROUP_BY SQL mode is enabled (default in MySQL 5.7+)
  4. Column is selected without being wrapped in an aggregate function or included in GROUP BY
  5. Using ORDER BY with non-aggregated columns that are not in GROUP BY

How to fix "ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause"

1Identify the problematic columns in your query

Review your SELECT statement to locate which columns are aggregate functions and which are plain columns. The error message will tell you the expression number and column name causing the issue.

sql
-- WRONG: Mixing aggregate (COUNT) with non-aggregate (name)
SELECT name, COUNT(*) FROM users;

-- This fails because MySQL cannot determine which user name to return
2Add GROUP BY for all non-aggregated columns

The most straightforward fix is to add a GROUP BY clause that includes all non-aggregated columns in your SELECT list. This makes the query deterministic by explicitly telling MySQL how to group the data.

sql
-- CORRECT: GROUP BY includes the non-aggregated column
SELECT name, COUNT(*) FROM users GROUP BY name;

-- Now MySQL knows to count occurrences of each distinct name

If you select multiple non-aggregated columns, include all of them in GROUP BY:

sql
SELECT department, job_title, COUNT(*) 
FROM employees 
GROUP BY department, job_title;
3Use ANY_VALUE() for columns where the specific value is irrelevant

If you need to select a non-aggregated column but its specific value doesn't matter (because all values in the group are the same), wrap it in ANY_VALUE(). This tells MySQL: "I know this column isn't grouped, but I accept any value from the group".

sql
-- Using ANY_VALUE() when you don't care which specific value is returned
SELECT department, ANY_VALUE(office_location), COUNT(*) 
FROM employees 
GROUP BY department;

-- Valid because all employees in a department share the same office_location

Use ANY_VALUE() carefully—only when values are guaranteed to be the same within each group.

4Leverage functional dependency on primary keys

If a non-aggregated column is functionally dependent on a GROUP BY column (typically because it's a primary key or unique key), MySQL recognizes this as valid without requiring GROUP BY.

sql
-- CORRECT: user_id is the primary key, so all columns from the same user_id are deterministic
SELECT u.id, u.name, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- Valid because u.name and u.email are functionally dependent on u.id (the primary key)
5Avoid disabling ONLY_FULL_GROUP_BY in production

While you can disable the ONLY_FULL_GROUP_BY SQL mode, this is not recommended for production environments:

sql
-- NOT RECOMMENDED - only for temporary testing
SET @@global.sql_mode := REPLACE(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', '');

Disabling this mode allows queries to run but produces non-deterministic results—the server arbitrarily chooses which value to return for non-grouped columns. This can cause:

- Data inconsistencies
- Unpredictable application behavior
- Hidden bugs that are difficult to debug
- Compatibility issues with modern MySQL standards

Instead, fix the query to be compliant with the strict mode.

Advanced notes

Window functions are an alternative solution for complex scenarios. If you need to display aggregate values alongside individual row data, use window functions like ROW_NUMBER() or RANK():

sql
SELECT id, name, salary, AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

This returns each individual employee row with the department average, without requiring GROUP BY.

Functional dependency rules are strict: a column must be a PRIMARY KEY, UNIQUE NOT NULL, or deterministically derived from a primary/unique key. Regular foreign keys or non-unique columns do not qualify for functional dependency.

The ONLY_FULL_GROUP_BY mode is part of MySQL's "strict" SQL compliance. Understanding GROUP BY semantics is crucial for writing portable, correct SQL across different database systems.

Frequently asked questions

What does "1140" mean?
7) to prevent queries that would return non-deterministic results. When you use an aggregate function like COUNT(), SUM(), MIN(), or MAX() in your SELECT list but also select non-aggregated columns, MySQL cannot determine which single value to return for those non-aggregated columns when multiple rows are involved. The error prevents ambiguous queries that could produce unpredictable or incorrect results.
How do I fix "1140"?
This error occurs when you mix aggregate functions (MIN, MAX, COUNT) with non-aggregated columns in SELECT without a GROUP BY clause. Fix it by adding GROUP BY for all non-aggregated columns, using ANY_VALUE(), or ensuring functional dependency on a primary key.
What causes "1140"?
SELECT list contains both aggregate functions (COUNT, MIN, MAX, SUM, AVG) and non-aggregated columns without GROUP BY Non-aggregated columns in SELECT are not included in the GROUP BY clause ONLY_FULL_GROUP_BY SQL mode is enabled (default in MySQL 5.7+)
Is the "1140" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading