SQL Puzzle – Correct the Incorrect Query – Win Price Worth USD 1000 – Aggregate and Subquery

SQL Puzzle - Correct the Incorrect Query - Win Price Worth USD 1000 - Aggregate and Subquery puzzle It has been a while since we have seen a puzzle so let us do a small puzzle today. Actually, this is a very simple puzzle if you have worked with SQL Server for a while. I got an email from one of the readers from the blog post and he wanted me to correct the following query which he had written. Well, I have been working with SQL Server for over 19 years and I often do not remember the syntax and have to look up online. Here is a very simple puzzle for you which involves Aggregate and Subquery.

Important Update: The Workshop is now over and it was one of the landmark workshops, where I shared business secrets of successful Performance Tuning Expert. You can watch the recording of the workshop for a limited period of time.

Puzzle – Aggregate and Subquery

Here is the query when we run against the AdventureWorks database, it gives us an error. Remember AdventureWorks database is a sample database and you can download and install AdventureWorks over here.

USE AdventureWorks2014
GO
SELECT pch.StandardCost, p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > AVG(p.StandardCost)
GO

Now when you run the above query, it gives us the following error:

Msg 147, Level 15, State 1, Line 17
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.

Now here is your task, you need to help me to fix the above query. The hint to resolve the query is already in the blog post’s title and in the query error. You need to understand what my query is attempting to do and use the hint which is already pointed out to you. After you successfully fix the query it will produce the following results.

SQL Puzzle - Correct the Incorrect Query - Win Price Worth USD 1000 - Aggregate and Subquery correctresult

Rules

Well, there are no real rules, as long as you modify my query to get me above results. Please post your query in the comments section below before April 22, 2019. I will keep all the answers hidden till April 22, 2019 11:59:00 PM.

Winner

One Winner will get 1 free seat for my upcoming class, which is worth USD 1000.

SQL Server Performance Tuning Practical Workshop for EVERYONE
Date: April 23, 2019, Tuesday
8:00 AM Pacific Time | 11:00 AM Eastern Time

If you do not want to take part in the contest, you can directly register for the class here.

Update: April 23, 2019

The contest is closed and the winning Answer is here:

USE AdventureWorks2014
GO
SELECT pch.ProductID, pch.StartDate,pch.EndDate, pch.StandardCost, pch.ModifiedDate
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.StandardCost
GO

There is no need for the AVG on the StandardCost as there will be always a single entry for the product. Even if you change the query with group by + having you will still see the query uses the simple join.

Congratulations to the winner Nicholas Månsson. He is very excited to join the SQL Server Performance Tuning Practical Workshop for EVERYONE.

Everyone who participated you got to solve this difficult puzzle so indirectly you are a winner (of knowledge) as well. This was an intense competition with over 500 comments.

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

Contest, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Relating Unrelated Tables – A Question from Reader
Next Post
Puzzle – DELETE Qualified Rows From Multiple Tables – Win USD 1000 Worth Class

Related Posts

527 Comments. Leave new

  • Hi Pinal,

    This is the answer

    USE AdventureWorks2014
    GO
    SELECT pch.StandardCost, p.ProductID
    FROM Production.ProductCostHistory pch
    INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
    WHERE pch.StandardCost > AVG(p.StandardCost)
    GROUP BY pch.StandardCost, p.ProductID HAVING
    pch.StandardCost > AVG(p.StandardCost)
    GO

    Reply
  • Damn! I thought I had the CORRECT answer until I saw the comments by Dave! :-D

    Reply
  • Gopinath Srirangan
    April 23, 2019 2:53 am

    Hi Pinal, I’m holding my nerves to see the answer :-) I didn’t use GROUP BY or HAVING clause but still eager to see the correct answer. Is the status still “Currently no one is selected” ? Do you think I can still try for correct answer ?

    Reply
  • Rajesh Vardhanapu
    April 23, 2019 3:14 am

    SELECT pch.*
    FROM Production.ProductCostHistory pch
    INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
    WHERE pch.StandardCost > p.StandardCost

    Reply
  • SELECT pch.ProductID, Pch.StartDate ,Pch.EndDate ,pch.StandardCost,pch.ModifiedDate
    FROM Production.ProductCostHistory pch
    WHERE pch.StandardCost > (SELECT AVG(p.StandardCost) From Production.Product p Where p.ProductID =pch.ProductID group by p.productID)

    Reply
  • Will that be the answer that silly then !?
    USE AdventureWorks2014
    GO
    SELECT pch.StandardCost, p.ProductID
    FROM Production.ProductCostHistory pch
    INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
    WHERE pch.StandardCost > (select AVG(StandardCost) from Production.Product)
    GO

    Reply
  • USE AdventureWorks2014
    GO

    SELECT p.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost, pch.modifieddate–, AVG(p.StandardCost)
    FROM Production.ProductCostHistory pch
    INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
    WHERE pch.StandardCost > (SELECT AVG(p.StandardCost) FROM Production.Product p where pch.ProductID = p.ProductID)
    GO

    GOT IT!

    Reply
  • SELECT p.ProductID, StartDate, EndDate, pch.StandardCost, p.ModifiedDate
    FROM Production.ProductCostHistory pch
    INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
    WHERE pch.StandardCost > p.StandardCost
    GO

    Reply
  • Mario Manzano
    April 23, 2019 6:04 am

    Hi Pinal,

    SELECT pch.StandardCost, p.ProductID
    FROM Production.ProductCostHistory pch
    INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
    WHERE pch.StandardCost > (SELECT AVG(StandardCost)
    FROM Production.Product
    WHERE ProductID = pch.ProductID
    GROUP BY ProductID)

    Cheers,
    Mario

    Reply
  • Daniel Espinoza Solórzano
    April 23, 2019 7:15 am

    USE AdventureWorks2014
    GO
    SELECT
    p.ProductId,
    pch.startdate,
    pch.enddate,
    pch.StandardCost,
    pch.ModifiedDate

    FROM Production.ProductCostHistory pch
    INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
    WHERE pch.StandardCost > (SELECT AVG(p1.StandardCost)
    FROM Production.ProductCostHistory pch1
    INNER JOIN Production.Product p1 ON pch1.ProductID = p1.ProductID and p1.ProductID = p.ProductID)

    Reply
  • Fernando Andrade
    April 23, 2019 7:22 am

    USE AdventureWorks2014
    GO
    SELECT
    p.ProductId,
    pch.startdate,
    pch.enddate,
    pch.StandardCost,
    pch.ModifiedDate

    FROM Production.ProductCostHistory pch
    INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
    WHERE pch.StandardCost > (SELECT AVG(p1.StandardCost)
    FROM Production.ProductCostHistory pch1
    INNER JOIN Production.Product p1 ON pch1.ProductID = p1.ProductID and p1.ProductID = p.ProductID)

    Reply
  • Kishore Reddy
    April 23, 2019 7:37 am

    Hi

    Below is Updated Query

    SELECT a.ProductID,pch.StartDate, pch.EndDate, pch.StandardCost, pch.ModifiedDate
    FROM
    (SELECT p.ProductID, AVG(P.StandardCost) AS AVGSTDCOST
    FROM Production.Product p
    GROUP BY p.ProductID)A
    INNER JOIN Production.ProductCostHistory pch ON A.ProductID=pch.ProductID
    WHERE pch.StandardCost>a.AVGSTDCOST

    Reply
  • Krushna Kadam
    April 23, 2019 8:50 am

    Good Morning Pinal,

    I have tried to find the solution for the given question.

    Please find attached below SQL Select statement for your verification.

    Could you please confirm it at your end.

    USE AdventureWorks2014

    GO

    SELECT p.ProductID,

    CAST(pch.StartDate AS DATETIME) AS startdate,

    CAST(pch.EndDate AS DATETIME) AS enddate,

    pch.StandardCost,

    CAST(pCH.ModifiedDate AS DATETIME) AS ModifiedDate

    FROM Production.ProductCostHistory pch

    INNER JOIN Production.Product p ON pch.ProductID = p.ProductID

    GROUP BY p.ProductID,

    CAST(pch.StartDate AS DATETIME),

    CAST(pch.EndDate AS DATETIME),

    CAST(pCH.ModifiedDate AS DATETIME),

    PCH.StandardCost

    HAVING pch.StandardCost > AVG(p.StandardCost)

    GO

    Thanks,
    Krushna

    Reply
  • Siddhesh Dhadve
    April 23, 2019 8:59 am

    As we are referring StandardCost from Product table, we will get the AVG StandardCost value of product same for every row in ProductCostHistory. So no need to apply AVG function. We will get the result by removing it from WHERE caluse

    USE AdventureWorks2014
    GO
    SELECT pch.StandardCost, p.ProductID
    FROM Production.ProductCostHistory pch
    INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
    WHERE pch.StandardCost > (p.StandardCost)
    GO

    Reply
  • Jija Dahifale
    April 23, 2019 9:10 am

    Hey…my answer was also correct. :)

    Reply
  • Hi All, the winning Answer is here:

    USE AdventureWorks2014
    GO
    SELECT pch.ProductID, pch.StartDate,pch.EndDate, pch.StandardCost, pch.ModifiedDate
    FROM Production.ProductCostHistory pch
    INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
    WHERE pch.StandardCost > p.StandardCost
    GO

    There is no need of the AVG on the standardcost as there will be always a single entry for the product. Even if you change the query with group by + having you will still see the query uses the simple join.

    The winner will be sent email in the next 10 minutes.

    Reply
    • Chandrabhan Singh Gurjar
      April 23, 2019 11:05 am

      Hi Pinal,
      My answer is also correct answer and waiting for your email to join the session.
      Looking forward to get it soon to take my skills to next level.

      Regards,
      CS

      Reply
  • Gopinath Srirangan
    April 23, 2019 9:26 am

    I think my answer is also correct.

    Reply
  • Hi All, the winning Answer is here:

    USE AdventureWorks2014
    GO
    SELECT pch.ProductID, pch.StartDate,pch.EndDate, pch.StandardCost, pch.ModifiedDate
    FROM Production.ProductCostHistory pch
    INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
    WHERE pch.StandardCost > p.StandardCost
    GO

    There is no need of the AVG on the standardcost as there will be always a single entry for the product. Even if you change the query with group by + having you will still see the query uses the simple join.

    Congratulations to the winner Nicholas MÃ¥nsson. He is very excited to join the SQL Server Performance Tuning Practical Workshop for EVERYONE.

    Everyone who participated you are winner as well, this was intense competition with over 500 comments.

    Congratulations!

    ~ Pinal at SQLAuthority.com

    Reply
    • Gopinath Srirangan
      April 23, 2019 11:26 am

      Hi Pinal, I’d like to know if my answer is correct though I’ve not used AVG and INNER JOIN.

      Reply
  • USE AdventureWorks2014
    GO
    SELECT pch.StandardCost, p.ProductID
    FROM Production.ProductCostHistory pch
    INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
    WHERE pch.StandardCost > (SELECT avg(subpch.StandardCost)
    FROM Production.ProductCostHistory subpch
    INNER JOIN Production.Product subp ON subpch.ProductID = subp.ProductID)

    Reply
  • Mera number kab aayega

    Reply

Leave a Reply