Fix “Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause” in SQL Server (Error 144)

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.