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

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.

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

-- This fails because MySQL cannot determine which user name to return

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.

-- 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:

SELECT department, job_title, COUNT(*) 
FROM employees 
GROUP BY department, job_title;

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".

-- 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.

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.

-- 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)

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

-- 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.