📋

Description

This error occurs when an `ORDER BY` clause, applied to the combined result of a `UNION`, `EXCEPT`, or `INTERSECT` operation, attempts to sort by an expression that directly includes an aggregate function (e.g., `COUNT()`, `SUM()`, `AVG()`). MySQL requires that the `ORDER BY` on a set operation's result refers to columns from the final merged set, not to aggregates from individual components.

💬

Error Message

Expression #%u of ORDER BY contains aggregate function and applies to a UNION, EXCEPT or INTERSECT

🔍

Known Causes

3 known causes

⚠️

Direct Aggregate in ORDER BY

You have directly specified an aggregate function within the `ORDER BY` clause that is sorting the combined result of a `UNION`, `EXCEPT`, or `INTERSECT` statement.

⚠️

Misunderstanding ORDER BY Scope

The `ORDER BY` clause applied to a `UNION` operates on the final, merged result set, and therefore cannot directly evaluate aggregates that would have been processed within individual `SELECT` statements.

⚠️

Incorrect Set Operation Logic

Assuming an `ORDER BY` with an aggregate function after a `UNION` will sort rows of each constituent `SELECT` before combination, which contradicts how MySQL processes these queries.

🛠️

Solutions

3 solutions available

1. Move Aggregate to Subquery medium

Encapsulate the aggregate function within a subquery to allow ordering.

1

Identify the `UNION` statement and the `ORDER BY` clause that uses an aggregate function.

2

Rewrite each part of the `UNION` to include the aggregate function in a subquery. The outer query will then select from these subqueries and apply the `ORDER BY` clause.

SELECT * FROM (
  SELECT column1, COUNT(*) as agg_count FROM table1 WHERE condition1 GROUP BY column1
  UNION ALL
  SELECT column2, COUNT(*) as agg_count FROM table2 WHERE condition2 GROUP BY column2
) AS combined_results
ORDER BY agg_count DESC;

3

Ensure the aggregate column has a consistent alias across all parts of the `UNION` for the outer `ORDER BY` to reference it correctly.

2. Order by Non-Aggregate Column First easy

If possible, order by a non-aggregate column before the aggregate.

1

Examine the `ORDER BY` clause in your `UNION` statement. If it contains multiple expressions, and one of them is a non-aggregate column, try reordering them.

2

Place a non-aggregate column expression before the aggregate function in the `ORDER BY` clause. MySQL might allow this if the non-aggregate column is sufficient for initial sorting.

SELECT column1, COUNT(*) as agg_count FROM table1 WHERE condition1 GROUP BY column1
UNION ALL
SELECT column2, COUNT(*) as agg_count FROM table2 WHERE condition2 GROUP BY column2
ORDER BY column1, agg_count DESC; -- Assuming column1 is not aggregated and is present in both selects

3

Verify that the non-aggregate column exists and is named consistently in all `SELECT` statements within the `UNION`.

3. Use a CTE for Complex Aggregations advanced

Employ Common Table Expressions (CTEs) to simplify and organize complex `UNION` queries with aggregations.

1

Define a CTE for each part of your `UNION` that involves aggregation. This isolates the aggregation logic.

WITH AggData1 AS (
  SELECT column1, COUNT(*) as agg_count FROM table1 WHERE condition1 GROUP BY column1
),
AggData2 AS (
  SELECT column2, COUNT(*) as agg_count FROM table2 WHERE condition2 GROUP BY column2
)
-- ... more CTEs if needed

2

Combine the CTEs using `UNION ALL` in the main query.

SELECT * FROM AggData1
UNION ALL
SELECT * FROM AggData2
ORDER BY agg_count DESC;

3

The `ORDER BY` clause in the main query can now reference the aggregate column alias defined within the CTEs.

🔗

Related Errors

5 related errors

MySQL Error 3028: Aggregate in UNION ORDER BY