In this blog post, let’s learn about the error message “147 – An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.
SQL Server Error Message
147 – An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Reason for the Error
Here’s an example that results in the SQL Server error Msg 147:
SELECT ProductID, Name, ListPrice FROM Production.Product WHERE AVG(ListPrice) > 50
The query above attempts to use the AVG() aggregate function in the WHERE clause, which SQL Server does not allow. Aggregate functions, such as AVG(), can only be used in the SELECT list or the HAVING clause. The WHERE clause is used to filter rows based on a criterion, and it does not support aggregate functions in its filtering logic. As a result, SQL Server returns error Msg 147.
Solution
To resolve this error, we need to use the aggregate function in the HAVING clause rather than the WHERE clause. Here’s an updated query that uses HAVING clause to filter out the rows based on the aggregate function:
SELECT ProductID, Name, AVG(ListPrice) AS AveragePrice FROM Production.Product GROUP BY ProductID, Name HAVING AVG(ListPrice) > 50
In the preceding query, we use the GROUP BY clause to group the products based on their ProductID and Name, and then calculate the AVG() of ListPrice for each group. Finally, we employ the HAVING clause to eliminate groups with an average ListPrice larger than 50. This query will produce the expected result with no errors.
