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

April 17, 2019

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.

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

SQL SERVER – Relating Unrelated Tables – A Question from Reader

Puzzle – DELETE Qualified Rows From Multiple Tables – Win USD 1000 Worth Class

527 Comments. Leave new

Michal Kovac
April 18, 2019 4:18 pm
USE AdventureWorks2014
GO
SELECT pSub.ProductID, StartDate, EndDate, pch.StandardCost, ModifiedDate
FROM Production.ProductCostHistory pch
INNER JOIN (select AVG(StandardCost) AvgStandardCost, ProductId from Production.Product group by ProductId) pSub on pSub.ProductID = pch.ProductID
WHERE pch.StandardCost > AvgStandardCost
Reply
MIke Wilson
April 18, 2019 4:22 pm
WITH AverageProductCosts_CTE AS
(
SELECT p.ProductID, AVG(p.StandardCost) AS AverageCost
FROM Production.Product
GROUP BY p.ProductID
)
SELECT pch.StandardCost, p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN AverageProductCosts_CTE p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.AverageCost;
Reply
rajeshkpatel
April 18, 2019 4:23 pm
select pch.StandardCost,p.ProductID
from Production.ProductCostHistory pch
inner join Production.Product p on pch.ProductID=p.ProductID
group by pch.StandardCost,p.ProductID
having pch.StandardCost>AVG(p.StandardCost)
Reply
Abhijit Chaubal
April 18, 2019 4:27 pm
Here the correct query:
SELECT Detail.*
FROM
(
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
) Detail
INNER JOIN
(
SELECT AVG(p.StandardCost) AvgCost, p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
GROUP BY p.ProductID
) AGG
ON Detail.ProductId = AGG.ProductId
WHERE Detail.StandardCost > AGG.AvgCost
Reply
Sumit Salunkhe
April 18, 2019 4:28 pm
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 pch1.ProductID = pch.ProductID
)
Reply
YAN
April 18, 2019 4:29 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)
SELECT pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost, pch.ModifiedDate
FROM Production.ProductCostHistory pch
WHERE EXISTS (
SELECT 1
FROM Production.Product p
WHERE pch.ProductID = p.ProductID
HAVING pch.StandardCost > AVG(p.StandardCost)
)
Reply
Suresh Kumar
April 18, 2019 4:34 pm
Hello Sir,
Below is the query which is same as your output and I have verified it by executing. Please check and confirm.
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 > (SELECT AVG(PP.StandardCost)
FROM Production.Product PP
WHERE PP.ProductID = PCH.ProductID)
ORDER BY P.ProductID
GO
Reply
HARSHIT RATHORE
April 18, 2019 4:35 pm
SELECT p.ProductID,StartDate,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
Alpana
April 18, 2019 4:36 pm
Hi ,
My solution is:
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
Anil
April 18, 2019 4:37 pm
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.StartDate,pch.EndDate,pch.StandardCost,pch.ModifiedDate
HAVING pch.StandardCost > AVG(p.StandardCost)
GO
Reply
Ondra L.
April 18, 2019 4:38 pm
Hi, i was trying to do some Partition and so on… NO! Once Production.Product has PK on ProductID we can say, that AVG of Production.Product.StandardCost will be always the same for each row, so why not to put out AVG out of query totally and run condition against always same value in (Production.Product.)StandardCost?
It does make sense… ;-) …and just little but modify an output to cover expected columns, voile:
SELECT p.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost, pch.ModifiedDate
–pch.StandardCost, p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.StandardCost
and result is like this: (external link removed)
Cheer,
Ondrej, Prague, Czech Republic
Reply
Rahul Tripathi
April 18, 2019 4:39 pm
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.Product p1 where p1.ProductID=p.ProductID)
order by p.productid
Reply
Durgadas
April 18, 2019 4:40 pm
Actually this one will give the correct result. No aggregation / No Subquery. :)
SELECT *
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.StandardCost
Reply
Randall Stone
April 18, 2019 4:41 pm
SELECT
p.ProductID
,pch.StartDate
,pch.EndDate
,pch.StandardCost
–,h.AvgCost
,pch.ModifiedDate
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p
ON pch.ProductID = p.ProductID
INNER JOIN (SELECT [ProductID]
,AVG([StandardCost]) AS AvgCost
FROM [Production].[ProductCostHistory]
GROUP BY ProductID) h
ON h.ProductID = pch.ProductID
WHERE pch.StandardCost > h.AvgCost
AND pch.EndDate = ‘2013-05-29’
AND p.StandardCost < 20.00
ORDER BY h.ProductID
Reply
Carl Soto
April 18, 2019 4:41 pm
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
group by p.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost, pch.ModifiedDate
having pch.StandardCost > AVG(p.StandardCost)
GO
Reply
vasu
April 18, 2019 4:42 pm
SELECT distinct pp.StandardCost, p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
inner join (select AVG(StandardCost) StandardCost,ProductID FROM Production.ProductCostHistory
group by ProductID) pp on p.ProductID =pp.ProductID
WHERE pch.StandardCost > pp.StandardCost
order by 2
Reply
Paul Bailey
April 18, 2019 4:43 pm
;WITH CTE AS (
SELECT ProductId = p.ProductID,
StandardCost = pch.StandardCost,
StartDate = pch.StartDate,
EndDate = pch.EndDate,
ModifiedDate = pch.ModifiedDate,
avg_StandardCost = AVG(p.StandardCost) OVER(Partition by p.StandardCost)
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p
ON pch.ProductID = p.ProductID
)
SELECT ProductId,StartDate,EndDate,StandardCost,ModifiedDate FROM CTE
WHERE cte.StandardCost > avg_StandardCost
ORDER BY productid
Reply
Jonathan Sparkes
April 18, 2019 4:46 pm
Hi,
Please see my version below:
SELECT
pch.ProductID,
pch.StartDate,
pch.EndDate,
pch.StandardCost,
pch.ModifiedDate
FROM
Production.ProductCostHistory pch
CROSS APPLY
(
SELECT AVG(StandardCost) AS StandardCost
FROM Production.Product
WHERE pch.ProductID = ProductID
GROUP BY ProductID
) p
WHERE
pch.StandardCost > p.StandardCost;
GO
Reply
Igor Mikovic
April 18, 2019 4:47 pm
SELECT pch.StandardCost, p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
GROUP BY p.ProductID, pch.StandardCost
HAVING pch.StandardCost > AVG(p.StandardCost)
Reply
Vimal Thakkar
April 18, 2019 4:49 pm
USE AdventureWorks2014
GO
SELECT pch.StandardCost, p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
inner join
(
SELECT AVG(pch.StandardCost) AvgStandardCost, p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
GROUP BY p.ProductID
) avgProd on avgProd.ProductID = pch.ProductID
WHERE pch.StandardCost > avgProd.AvgStandardCost
GO
Reply