SQL SERVER – Fix: Error: 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
- by pinaldave
Everybody was beginner once and I always like to get involved in the questions from beginners. There is a big difference between the question from beginner and question from advanced user. I have noticed that if an advanced user gets an error, they usually need just a small hint to resolve the problem. However, when a beginner gets error he sometimes sits on the error for a long time as he/she has no idea about how to solve the problem as well have no idea regarding what is the capability of the product. I recently received a very novice level question. When I received the problem I quickly see how the user was stuck. When I replied him with the solution, he wrote a long email explaining how he was not able to solve the problem. He thanked multiple times in the email. This whole thing inspired me to write this quick blog post.
I have modified the user’s question to match the code with AdventureWorks as well simplified so it contains the core content which I wanted to discuss.
Problem Statement: Find all the details of SalesOrderHeaders for the latest ShipDate.
He comes up with following T-SQL Query:
SELECT *
FROM [Sales].[SalesOrderHeader]
WHERE ShipDate = MAX(ShipDate)
GO
When he executed above script it gave him following error:
Msg 147, Level 15, State 1, Line 3
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.
He was not able to resolve this problem, even though the solution was given in the query description itself. Due to lack of experience he came up with another version of above query based on the error message.
SELECT *
FROM [Sales].[SalesOrderHeader]
HAVING ShipDate = MAX(ShipDate)
GO
When he ran above query it produced another error.
Msg 8121, Level 16, State 1, Line 3
Column ‘Sales.SalesOrderHeader.ShipDate’ is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
What he wanted actually was the SalesOrderHeader all the Sales shipped on the last day. Based on the problem statement what the right solution is as following, which does not generate error.
SELECT *
FROM [Sales].[SalesOrderHeader]
WHERE ShipDate = (SELECT MAX(ShipDate) FROM [Sales].[SalesOrderHeader])
Well, that’s it! Very simple. With SQL Server there are always multiple solution to a single problem. Is there any other solution available to the problem stated? Please share in the comment.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
