📋

Description

This error occurs when an `ORDER BY` clause attempts to use an aggregate function (e.g., `SUM()`, `COUNT()`, `AVG()`) within a query that is not itself an aggregated query. MySQL cannot determine how to order individual rows based on a single aggregate value that applies to the entire result set or a different grouping context without a `GROUP BY` clause.

💬

Error Message

Expression #%u of ORDER BY contains aggregate function and applies to the result of a non-aggregated query

🔍

Known Causes

3 known causes

⚠️

Aggregate in Ungrouped Query

An aggregate function (e.g., `SUM()`, `COUNT()`) is directly included in the `ORDER BY` clause of a `SELECT` statement that lacks a corresponding `GROUP BY` clause.

⚠️

Mismatched Query Context

The query attempts to sort individual rows using an aggregate calculation that logically applies to the entire result set or to defined groups, creating a conflict with row-level ordering.

⚠️

Misunderstanding ORDER BY Scope

Developers might incorrectly assume an aggregate function in `ORDER BY` will yield a per-row value for sorting, rather than a single value for the entire set or a specific group.

🛠️

Solutions

4 solutions available

1. Remove Aggregate from ORDER BY easy

The simplest fix is to remove the aggregate function from the ORDER BY clause if it's not needed.

1

Identify the aggregate function (e.g., SUM(), COUNT(), AVG(), MAX(), MIN()) in your ORDER BY clause.

2

Remove the aggregate function and the column it's applied to from the ORDER BY clause. If you need to sort by a derived value, consider calculating it within the SELECT list without aggregation or in a subquery.

Original Query (Illustrative):
SELECT department, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department
ORDER BY COUNT(employee_id) DESC;

Modified Query:
SELECT department, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department
ORDER BY department DESC;

2. Use a Subquery for Aggregated Sorting medium

Encapsulate the aggregation in a subquery to allow sorting by the aggregated result.

1

Create a derived table (subquery) that performs the aggregation.

SELECT department, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department;

2

Select from the derived table and apply the ORDER BY clause to the aggregated column.

SELECT department, employee_count
FROM (
    SELECT department, COUNT(employee_id) AS employee_count
    FROM employees
    GROUP BY department
) AS aggregated_data
ORDER BY employee_count DESC;

3. Include Non-Aggregated Columns in ORDER BY medium

If you need to sort by an aggregate, ensure all non-aggregated columns from the SELECT list are also present in the ORDER BY clause.

1

Identify all columns in your SELECT list that are NOT aggregate functions.

2

Add these non-aggregated columns to your ORDER BY clause, preceding the aggregate function.

Original Query (Illustrative):
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY AVG(salary) DESC;

Modified Query:
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY department ASC, AVG(salary) DESC;

4. Use a Window Function (MySQL 8.0+) advanced

Leverage window functions to perform calculations over a set of table rows that are related to the current row, allowing aggregation in the ORDER BY clause without a GROUP BY.

1

Rewrite your query using a window function (e.g., `SUM() OVER (...)`, `COUNT() OVER (...)`) to compute the aggregate for each row.

SELECT
    employee_id,
    department,
    salary,
    COUNT(*) OVER (PARTITION BY department) AS department_employee_count
FROM employees
ORDER BY department_employee_count DESC;

2

If the intention was to aggregate per department and sort, the window function provides the aggregated value without needing an explicit GROUP BY, allowing direct sorting.

🔗

Related Errors

5 related errors

MySQL Error 3029: Aggregate in Non-Aggregated ORDER BY