If you’re getting an error that reads something like “Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.” in SQL Server, it looks like you’re trying to use either an aggregate function or a subquery in the GROUP BY clause.

We can’t use aggregates or subqueries in the GROUP BY clause.

To fix this issue, remove any aggregate functions or subqueries from your GROUP BY clause.

Example of Error

Here’s an example of code that results in the error:

SELECT
    CountryCode,
    District,
    SUM(Population) AS Population
FROM City
WHERE CountryCode IN ('AGO', 'ARE', 'AUS')
GROUP BY CountryCode, District, SUM(Population)
ORDER BY CountryCode;

Output:

Msg 144, Level 15, State 1, Line 7
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

As expected, we got an error. That’s because I included an aggregate function in the GROUP BY clause.

Solution

As mentioned, to fix this issue, remove any aggregate functions or subqueries from your GROUP BY clause.

Example:

SELECT
    CountryCode,
    District,
    SUM(Population) AS Population
FROM City
WHERE CountryCode IN ('AGO', 'ARE', 'AUS')
GROUP BY CountryCode, District
ORDER BY CountryCode;

Output:

CountryCode  District              Population
-----------  --------------------  ----------
AGO          Benguela              258300    
AGO          Huambo                163100    
AGO          Luanda                2022000   
AGO          Namibe                118200    
ARE          Abu Dhabi             624665    
ARE          Ajman                 114395    
ARE          Dubai                 669181    
ARE          Sharja                320095    
AUS          Capital Region        322723    
AUS          New South Wales       3993949   
AUS          Queensland            1805236   
AUS          South Australia       978100    
AUS          Tasmania              126118    
AUS          Victoria              2990711   
AUS          West Australia        1096829   

This time the query ran as expected.