SQL SERVER – Fix: Error 147 An aggregate may not appear in the WHERE clause

SQL SERVER - Fix: Error 147 An aggregate may not appear in the WHERE clause document-error-flat Everybody was a beginner once and I always like to get involved in the questions from beginners. There is a big difference between the question for 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. Let us learn about how to fix Error 147 in this blog post.

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.

Solarwinds

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 actually wanted 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. Are there any other solution available to the problem stated? Please share in the comment.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – A Puzzle Part 4 – Fun with SEQUENCE in SQL Server 2012 – Guess the Next Value
Next Post
SQL SERVER – Simple Example to Configure Resource Governor – Introduction to Resource Governor

Related Posts

15 Comments. Leave new

  • Manjuke Fernando
    June 3, 2012 8:26 pm

    How about using a Common TablenExpression, instead of having the inline sub query?

    Reply
  • Manjuke Fernando
    June 3, 2012 8:28 pm

    ** Common Table Expression. Sorry for the spelling mistake..

    Reply
  • Alexey Rokhin
    June 4, 2012 9:59 am

    select top (1) with ties * from [Sales].[SalesOrderHeader] order by ShipDate desc

    Reply
    • Nice alternative, especially if I have to sort by “Ship Date” once

      Reply
      • 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).

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

    Reply
  • Hello pinal,

    its very nice post.

    There is one another way to do this.

    Reply
  • 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

    Reply
  • 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.

    Reply
  • Chirag Satasiya
    June 6, 2012 5:31 pm

    Hi pinal sir,

    Alternative to above solution :

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

    Reply
  • Venkat Kilari
    June 15, 2012 3:43 pm

    Hi Sir,

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

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

    Reply
  • Amit Srivastava
    June 19, 2012 3:17 pm

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

    Reply
  • thanks boss it work from me in this my case

    Reply
  • Amazin!!!, i was stcke for the same mistake, so helpful!!!!

    Reply
  • @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.

    Reply

Leave a Reply

Menu