📋

Description

MySQL Error 3066 occurs when an `ORDER BY` clause attempts to sort results using an aggregate function (e.g., COUNT(), SUM()) that is not explicitly included in the `SELECT` list. This typically happens when MySQL's strict SQL modes are active, enforcing that all expressions used for ordering must either be selected or part of a `GROUP BY` clause, especially with aggregates.

💬

Error Message

Expression #%u of ORDER BY clause is not in SELECT list, contains aggregate function; this is incompatible with %s

🔍

Known Causes

3 known causes

⚠️

Aggregate Not in SELECT List

You are attempting to sort your query results by an aggregate function (e.g., AVG(), MAX()) that has not been explicitly included in the `SELECT` statement.

⚠️

Strict SQL Mode Enforcement

MySQL is running in a strict SQL mode, such as `ONLY_FULL_GROUP_BY`, which requires all non-aggregated expressions in the `ORDER BY` clause to also appear in the `SELECT` list or `GROUP BY` clause.

⚠️

Misunderstanding Query Logic

The query design implicitly expects an aggregate value to be available for ordering without explicitly making it part of the query's output or intermediate calculation.

🛠️

Solutions

3 solutions available

1. Include Aggregate in SELECT List easy

Add the aggregate function used in ORDER BY to the SELECT list.

1

Identify the aggregate function and the column it's applied to in your ORDER BY clause. For example, if your ORDER BY clause is `ORDER BY COUNT(column_name)`, you need to include `COUNT(column_name)` in your SELECT list.

2

Modify your SQL query to include the aggregate expression in the SELECT list. It's good practice to give the aggregate an alias for clarity.

SELECT column1, column2, COUNT(column_name) AS count_of_column
FROM your_table
WHERE some_condition
GROUP BY column1, column2
ORDER BY COUNT(column_name) DESC;

2. Remove Aggregate from ORDER BY Clause easy

Remove the aggregate function from the ORDER BY clause if it's not necessary for ordering.

1

Examine your ORDER BY clause. If the aggregate function in the ORDER BY is not essential for the desired sorting order, remove it.

2

Rewrite the query without the aggregate in the ORDER BY clause. If you still need to order by a specific column that was part of the aggregate's logic, use that column directly.

SELECT column1, column2, COUNT(column_name) AS count_of_column
FROM your_table
WHERE some_condition
GROUP BY column1, column2
ORDER BY column1 ASC;

3. Use a Subquery or Derived Table medium

Perform aggregation in a subquery and then order the results of the subquery.

1

Create a subquery (or derived table) that performs the aggregation and selects the necessary columns, including the aggregate expression.

SELECT column1, column2, COUNT(column_name) AS count_of_column
FROM your_table
WHERE some_condition
GROUP BY column1, column2

2

In the outer query, select from the subquery and apply the ORDER BY clause to the columns available in the subquery's SELECT list.

SELECT column1, column2, count_of_column
FROM (
    SELECT column1, column2, COUNT(column_name) AS count_of_column
    FROM your_table
    WHERE some_condition
    GROUP BY column1, column2
) AS aggregated_data
ORDER BY count_of_column DESC;

🔗

Related Errors

5 related errors

MySQL Error 3066: Aggregate in ORDER BY Not in SELECT