Sep 2025 Discount: Comprehensive Database Performance Health Check | Testimonials

Home

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

Pinal Dave
SQL Puzzle
527 Comments

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

  • balakrishna141
    April 18, 2019 11:32 pm

    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

    GROUP BY pch.ProductID
    ,pch.StartDate
    ,pch.EndDate
    ,pch.StandardCost
    ,pch.ModifiedDate
    HAVING pch.StandardCost > AVG(p.StandardCost)

    Reply
  • georgemcmullan
    April 18, 2019 11:50 pm

    This one worked for me, not sure if it’s the expected query solution though?

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

    Reply
  • Honey
    April 19, 2019 12:01 am

    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)

    Reply
  • MB SQL
    April 19, 2019 12:03 am

    –Sorry Dave…

    –I believe the answer to your clients question could be 3 different result sets depending on which average you actually need.
    –See both examples below (none of my queries return the same result set as your answer above and the only way I could accomplish your result set would be to compare where the ProductCostHistory was higher than the Product.StandardCost value for the given product id, which doesn’t seem like an accurate result set, please check to see if I’m correct)

    –P.S. I would really love that free spot in your class!
    –My Best,
    –Matthew

    –Result Set 1, return the product history where they are greater than the total average of Productuction.Products.StandardCost

    CREATE INDEX [IX.Production.Product.StandardCost_INCLUDE] ON Production.Product (ProductId) INCLUDE (StandardCost)

    CREATE INDEX [IX.Production.ProductCostHistory.StandardCost_INCLUDE] ON Production.ProductCostHistory (StandardCost) INCLUDE (ProductId,StartDate,EndDate)

    SELECT
    p.ProductID
    ,GreaterThanAvg.StartDate
    ,GreaterThanAvg.EndDate
    ,GreaterThanAvg.StandardCost
    ,ProductAvgCost = ProductAvg.StandardCost
    FROM Production.Product p WITH (INDEX(PK_Product_ProductID))
    CROSS APPLY
    (
    SELECT
    StandardCost = AVG(p2.StandardCost)
    FROM Production.Product p2
    ) ProductAvg
    CROSS APPLY
    (
    SELECT TOP 1
    pch.StartDate
    ,pch.EndDate
    ,pch.StandardCost
    FROM Production.ProductCostHistory pch
    WHERE
    pch.ProductID = p.ProductID
    AND
    pch.StandardCost > ProductAvg.StandardCost
    ORDER BY
    pch.StandardCost DESC
    ) GreaterThanAvg
    ORDER BY
    p.ProductID;

    –Result Set 2, return the product history where the standard cost is greater than the total average of Productuction.ProductCostHistory.StandardCost for that given product Id.
    SELECT
    p.ProductID
    ,GreaterThanAvg.StartDate
    ,GreaterThanAvg.EndDate
    ,GreaterThanAvg.StandardCost
    ,ProductAvgCost = ProductAvg.StandardCost
    FROM Production.Product p WITH (INDEX(PK_Product_ProductID))
    CROSS APPLY
    (
    SELECT
    StandardCost = AVG(p2.StandardCost)
    FROM Production.ProductCostHistory p2
    WHERE
    p2.ProductID = p.ProductID
    ) ProductAvg
    CROSS APPLY
    (
    SELECT TOP 1
    pch.StartDate
    ,pch.EndDate
    ,pch.StandardCost
    FROM Production.ProductCostHistory pch
    WHERE
    pch.ProductID = p.ProductID
    AND
    pch.StandardCost > ProductAvg.StandardCost
    ORDER BY
    pch.StandardCost DESC
    ) GreaterThanAvg
    ORDER BY
    p.ProductID;

    –Result Set 3, return the product history where the standard cost is greater than the total average of Productuction.ProductCostHistory.StandardCost for ALL ProductCostHistory.
    SELECT
    p.ProductID
    ,GreaterThanAvg.StartDate
    ,GreaterThanAvg.EndDate
    ,GreaterThanAvg.StandardCost
    ,ProductAvgCost = ProductAvg.StandardCost
    FROM Production.Product p WITH (INDEX(PK_Product_ProductID))
    CROSS APPLY
    (
    SELECT
    StandardCost = AVG(p2.StandardCost)
    FROM Production.ProductCostHistory p2
    ) ProductAvg
    CROSS APPLY
    (
    SELECT TOP 1
    pch.StartDate
    ,pch.EndDate
    ,pch.StandardCost
    FROM Production.ProductCostHistory pch
    WHERE
    pch.ProductID = p.ProductID
    AND
    pch.StandardCost > ProductAvg.StandardCost
    ORDER BY
    pch.StandardCost DESC
    ) GreaterThanAvg
    ORDER BY
    p.ProductID;

    Reply
  • Jayraj
    April 19, 2019 12:10 am

    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(StandardCost) FROM Production.Product WHERE [p].[ProductID] = [ProductID])
    GO

    Reply
  • MUHAMMAD KASHIF FAIZ
    April 19, 2019 12:17 am

    Select pch.*,P.cost from Production.ProductCostHistory pch
    Inner Join
    (Select ProductID,Avg(StandardCost) as Cost from Production.ProductCostHistory Group by ProductID) as
    P
    On P.ProductID=pch.ProductID
    Where pch.StandardCost>cost

    Reply
  • gurunath kalburgi
    April 19, 2019 12:20 am

    This will give same result which you posted

    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
    GROUP BY P.PRODUCTID,PCH.STANDARDCOST,pch.StartDate,pch.EndDate,pch.ModifiedDate
    HAVING PCH.STANDARDCOST > AVG(P.STANDARDCOST)

    If we are looking for productionwise avg cost. (This will give some additional records which also content above query result set)
    with ProductCostAvg AS
    (
    select ProductID, avg(StandardCost) as AvgStandardCost from Production.ProductCostHistory group by ProductID
    )
    SELECT p.ProductID,StartDate,EndDate, pch.StandardCost, pch.ModifiedDate
    FROM Production.ProductCostHistory pch
    INNER JOIN ProductCostAvg avgp ON pch.ProductID = avgp.ProductID and pch.StandardCost > avgp.AvgStandardCost
    INNER JOIN Production.Product p ON avgp.ProductID = p.ProductID
    WHERE EndDate IS NOT NULL
    order by p.ProductID,pch.StandardCost asc

    Reply
  • Somnath Chowdhury
    April 19, 2019 12:29 am

    USE AdventureWorks2014
    Go
    Select pch.StandardCost, p.ProductID
    from Production.ProductCostHistory pch
    Inner Join (Select ProductID , Avg(StandardCost) AvgStdCost from Production.Product group by ProductID) p on pch.ProductID = p.ProductID
    Where pch.StandardCost > p.AvgStdCost
    Go
    I think this should do it.

    Reply
  • John J DiMartino
    April 19, 2019 12:36 am

    I submitted the query puzzle response with my querry script that does provide the correct result set . Did you not read the e-mail?

    Reply
    • Pinal Dave
      April 19, 2019 10:03 am

      Hi John,

      I do read the email but it is important to have all the answers in a single place so in the future people can read it together.

      Reply
  • Stephen Carpenter
    April 19, 2019 12:45 am

    Seems like a good place for a CTE:

    ;WITH ProductAverageCost
    AS
    (SELECT ProductId, AVG(StandardCost) AS AverageCost FROM Production.Product GROUP BY ProductID)
    SELECT p.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost,pch.ModifiedDate
    FROM Production.ProductCostHistory pch
    INNER JOIN ProductAverageCost p ON p.ProductID = pch.ProductID
    WHERE pch.StandardCost > p.AverageCost

    Reply
  • Muthu
    April 19, 2019 12:58 am

    Hi Pinal,

    This query will present the result as given,

    SELECT pch.*
    FROM Production.ProductCostHistory pch
    WHERE pch.StandardCost > (SELECT AVG(p.StandardCost)
    FROM Production.Product p
    WHERE p.ProductID=pch.ProductID)

    Reply
  • gordana2017Goca
    April 19, 2019 1:05 am

    SELECT p.ProductID, StartDate, EndDate, p.StandardCost, p.ModifiedDate
    FROM Production.ProductCostHistory pch
    INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
    WHERE pch.StandardCost > (select AVG(StandardCost) from Production.Product )
    order by ProductID

    Reply
  • Jacob
    April 19, 2019 1:38 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(a.StandardCost) FROM Production.Product a WHERE pch.ProductID = a.ProductID GROUP BY a.StandardCost)
    GO

    Reply
  • Jacob
    April 19, 2019 1:40 am

    Although it provided the same results, I realized there was a mistake in my previous query.

    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(a.StandardCost) FROM Production.Product a WHERE pch.ProductID = a.ProductID GROUP BY a.ProductID)
    GO

    Reply
  • Aaron Allen
    April 19, 2019 1:47 am

    SELECT m.ProductId, m.StartDate, m.EndDate, m.StandardCost, m.ModifiedDate
    FROM
    (SELECT pch.StandardCost, p.ProductID, pch.StartDate, pch.EndDate, AVG(p.StandardCost) AS AvgStandardCost, pch.ModifiedDate
    FROM Production.ProductCostHistory pch
    INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
    GROUP BY pch.StandardCost, p.ProductID, pch.StartDate, pch.EndDate, pch.ModifiedDate
    HAVING pch.StandardCost > AVG(p.StandardCost)) AS m

    Reply
  • Enoch Daniel
    April 19, 2019 2:02 am

    Just a typo:
    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 > p.StandardCost

    Reply
  • Chris Harshman
    April 19, 2019 2:24 am

    Maybe I misunderstood the requirements, but since ProductID is primary key of Production.Product table, the AVG function is not needed since there will be only one value of p.StandardCost for a given ProductID:

    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

    Reply
  • Kyle Walker
    April 19, 2019 2:32 am

    –TWO METHODS!

    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(p2.StandardCost) FROM Production.Product p2 WHERE p2.ProductID = pch.ProductID)
    ORDER BY ProductID
    GO

    –Or

    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
    INNER JOIN (SELECT AVG(StandardCost) as AvgStandardCost, ProductID FROM Production.Product GROUP BY ProductID) p2 ON p.ProductID = p2.ProductID
    WHERE pch.StandardCost > p2.AvgStandardCost
    ORDER BY ProductID
    GO

    Reply
  • A. Iqbal
    April 19, 2019 2:41 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 > p.StandardCost
    Go

    Reply
  • Henry Stinson
    April 19, 2019 2:46 am

    Since the original query did not have any requirements for date ranges, I am ignoring your requested addition of such. The original query should work like this:

    USE AdventureWorks2016CTP3;
    GO

    WITH CTE_1 AS (
    SELECT pch.StandardCost
    , p.ProductID
    ,AVG(pch.StandardCost) OVER (PARTITION BY p.ProductID ORDER BY p.ProductID) AS AverageCost
    FROM Production.ProductCostHistory pch
    INNER JOIN Production.Product p
    ON pch.ProductID = p.ProductID
    )
    SELECT StandardCost
    ,ProductID
    FROM CTE_1
    WHERE StandardCost > AverageCost
    GO

    Reply
« Older Comments Newer Comments »

Leave a Reply

Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 22 years of hands-on experience. He holds a Masters of Science degree and numerous database certifications.

Pinal has authored 14 SQL Server database books and 99 Pluralsight courses. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,800 database tech articles on his blog at https://blog.sqlauthority.com.

Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. If you need help with any SQL Server Performance Tuning Issues, please feel free to reach out at pinal@sqlauthority.com.

Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).

Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long-term multi-channel campaigns to drive leads for their sales pipeline.

Comprehensive Database Performance Health Check

Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.

Once you learn my business secrets, you will fix the majority of problems in the future.

SQL Server Performance Tuning Practical Workshop

Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.

Essentially I share my business secrets to optimize SQL Server performance.