SQL Server Error Msg 144 – Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause

In this blog post, let’s learn about the error message “144 – Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.

SQL Server Error Message

144 – Cannot use an agregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Reason for the Error

SQL Server Error Msg 144 is thrown when a column in a SELECT statement’s GROUP BY clause contains an aggregate function or a subquery. Because SQL Server does not allow the use of aggregate functions or subqueries in the GROUP BY clause, this error arises.

Here’s an example of how this may happen:

SELECT CustomerId, SUM(TotalDue)
FROM Sales.SalesOrderHeader
GROUP BY SUM(TotalDue)

This statement attempts to group the Sales.SalesOrderHeader table by the total due amount of each order, and then calculate the total due amount for each customer. However, this statement includes the SUM function in the GROUP BY clause, which is not allowed.

Solution

To fix this error, we need to modify the GROUP BY clause to include only the non-aggregated columns. In this example, we need to remove the SUM function from the GROUP BY clause and group by the CustomerIdcolumn instead:

SELECT CustomerId, SUM(TotalDue)
FROM Sales.SalesOrderHeader
GROUP BY CustomerId