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.