SQL Server Error Msg 130 – Cannot perform an aggregate function on an expression containing an aggregate or a subquery

In this blog post, let’s learn about the error message “130 – Cannot perform an aggregate function on an expression containing an aggregate or a subquery.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.

SQL Server Error Message

130 – Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Reason for the Error

SQL Server Error Msg 130 occurs when an aggregate function is used on an expression that already contains another aggregate function or subquery. In other words, you cannot nest aggregate functions or subqueries within another aggregate function.

Here is a simple example to reproduce the error:

SELECT MAX(SUM([DepartmentID]))
FROM [HumanResources].[Department]

Solution

To fix this error, you need to modify the query so that you’re not trying to perform an aggregate function on an expression that already contains an aggregate or subquery.

In the given example, the issue is with the expression inside the MAX function – it contains the SUM function which is itself an aggregate function. To fix this, you can first compute the sum in a subquery or a common table expression (CTE), and then apply the MAX function to the result.

Here’s an example query that would avoid the error:

WITH DepartmentSums AS (
  SELECT SUM([DepartmentID]) AS DepartmentSum
  FROM [HumanResources].[Department]
)
SELECT MAX(DepartmentSum) 
FROM DepartmentSums;

This query first computes the sum of DepartmentID values using the SUM function in a subquery or CTE called DepartmentSums. Then, it selects the maximum value of DepartmentSum using the MAX function applied to the result of the subquery or CTE.

By separating the aggregation steps into separate subqueries or CTEs, you can avoid the error message and perform the desired computation.