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

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)

15 thoughts on “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

      • I would say this is the right way to do it. Not to forget that MAX function will have a significant impact on the overall performance (in the absence of a nolock it has the potential of locking the table as well – ofc this is only when the table contains large amount of data).

        Like

  1. Is there any performance issue if the data is growth in SalesOrderHeader, because we are using same table reference (SalesOrderHeader)

    Like

  2. WITH Max_Ship_Date AS (
    SELECT Date_Shipped = MAX(ShipDate)
    FROM SalesOrderHeader
    )
    SELECT *
    FROM SalesOrderHeader
    INNER JOIN Max_Ship_Date ON
    SalesOrderHeader.ShipDate = Max_Ship_Date.Date_Shipped

    Like

  3. There are multiple ways to do the same thing in SQL. One of the things one could do is instead of thinking in syntactical fashion is to start with the data and see where one needs to get to that could help one to get different solutions.

    Like

  4. Hi pinal sir,

    Alternative to above solution :

    SELECT TOP 1 * FROM [Sales].[SalesOrderHeader] ORDER BY ShipDate DESC

    Like

  5. Hi Sir,

    SELECT TOP 1 * FROM [Sales].[SalesOrderHeader] ORDER BY ShipDate DESC

    I Think this is the best way in performance wise..

    Like

  6. with cte as
    (select row_number over(order by ShipDate DESC)ID,* from [Sales].[SalesOrderHeader])
    Select * from cte where id=1

    Like

  7. @Pinal Dave – Getting a solution is great. But, it would be better if you could also include the reasons for the errors. Once one knows the reasons, one will not make the same mistake. Otherwise, one will have to memorize this and it can bite you in SQL interviews.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s