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

Partha Mandayam
April 18, 2019 5:08 pm
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
Stephan Dahlhaus
April 18, 2019 5:10 pm
USE AdventureWorks2014
GO
SELECT pch.StandardCost, p.ProductID
FROM
Production.ProductCostHistory pch
INNER JOIN (SELECT ProductID, AVG(StandardCost) AS AVGStandardCost FROM Production.Product
GROUP BY ProductID) AS p
ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.AVGStandardCost
GO
Reply
Willem Jan Bethlehem
April 18, 2019 5:12 pm
USE AdventureWorks2014
GO
SELECT p.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost, pch.ModifiedDate
FROM Production.ProductCostHistory AS pch
INNER JOIN Production.Product AS p
ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > (SELECT AVG(p.StandardCost) FROM Production.Product p) and pch.ModifiedDate = ‘2013-05-29 00:00:00.000’
GO
Reply
Venu Yankarla
April 18, 2019 5:13 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(p2.StandardCost) AvgCost from Production.Product P2 Where P2.ProductID=P.ProductID)
Reply
John Raghanti
April 18, 2019 5:16 pm
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)
Reply
Rohit Kumar
April 18, 2019 5:20 pm
I had posted my answer earlier but if I consider your desired result set then I must include required columns as well. So the query will restructured as below:-
USE AdventureWorks2014
GO
SELECT p.ProductID, p.StartDate, , p.EndDate, pch.StandardCost, p.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 pch.ProductID = p1.ProductID)
Reply
Mark Tremaine
April 18, 2019 5:24 pm
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) AS StandardCost FROM Production.ProductCostHistory)
Reply
arbaaz
April 18, 2019 5:25 pm
SELECT pch.ProductID,pch.StartDate,pch.EndDate, pch.StandardCost,pch.ModifiedDate
FROM Production.ProductCostHistory pch
where pch.StandardCost > (select AVG(StandardCost) from Production.Product where ProductID=pch.ProductID )
I have executed it and it is returning correct result set. Just let me know if it is an acceptable answer or should I keep on looking?
Reply
rabe01
April 18, 2019 5:26 pm
SELECT pch.StandardCost, p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN (Select ProductID, AVG(StandardCost) as avgC from Production.Product group by ProductID) p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.avgC
GO
with p as (Select ProductID, AVG(StandardCost) as avgC from Production.Product group by ProductID)
SELECT p.ProductID, StartDate,EndDate,pch.StandardCost,ModifiedDate
FROM Production.ProductCostHistory pch
INNER JOIN p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.avgC
Reply
Suma
April 18, 2019 5:26 pm
Hi Pinal,
I have modified the query to get the two column values ProductID and StandardCost.
But in output, you have specified some date fields too. Do I need to select that too?
USE AdventureWorks2014
GO
SELECT p.ProductID,pch.StandardCost
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)
GO
And the output will be:
ProductID StandardCost
858 9.7136
859 9.7136
860 9.7136
707 13.8782
708 13.8782
711 13.8782
Reply
Partha Pratim Dinda
April 18, 2019 5:28 pm
Hi Pinal ,
Both of below query will out put same as you expected . Both doing Index scan but according to performance 1 st query is a better performer .
SELECT p.ProductID,pch.StartDate,pch.EndDate,pch.StandardCost,pch.ModifiedDate
FROM Production.ProductCostHistory pch
INNER JOIN (select p2.ProductID, AVG(p2.StandardCost) StandardCost FROM Production.Product p2
group by ProductID ) as p on p.ProductID=pch.ProductID
where pch.StandardCost> p.StandardCost
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(p2.StandardCost) FROM Production.Product p2 where p2.ProductID=p.ProductID)
GO
Thanks,
Partha
Reply
ANGELO MONACHESI
April 18, 2019 5:29 pm
I didn’t download the db, but I guess the easiest way is that to introduce a sub query for having the average costs, something like:
USE AdventureWorks2014
GO
SELECT pch.StandardCost, p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN
(SELECT ProductID, AVG(StandardCost) as AVGStandardCost from Production.Product GROUP BY ProductID) p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > AVGStandardCost
GO
Reply
pawan
April 18, 2019 5:29 pm
SELECT pch.StandardCost, p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN
(select AVG(p1.StandardCost) avg_cost,ProductID from Production.Product p1 group by ProductID) p
ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.avg_cost
Reply
Murtaza M
April 18, 2019 5:31 pm
With having clause…
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)
With subquery…
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 = p.ProductID
Reply
sali ali
April 18, 2019 5:32 pm
SELECT pch.StandardCost,p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
CROSS APPLY (SELECT AVG(StandardCost) AS StandardCostAvg
FROM Production.ProductCostHistory
WHERE Production.ProductCostHistory.ProductID = pch.ProductID
GROUP BY Production.ProductCostHistory.ProductID
) avgCost
WHERE pch.StandardCost > avgCost.StandardCostAvg
GO
Reply
salim.ali
April 18, 2019 5:32 pm
SELECT pch.StandardCost,p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
CROSS APPLY (SELECT AVG(StandardCost) AS StandardCostAvg
FROM Production.ProductCostHistory
WHERE Production.ProductCostHistory.ProductID = pch.ProductID
GROUP BY Production.ProductCostHistory.ProductID
) avgCost
WHERE pch.StandardCost > avgCost.StandardCostAvg
GO
Reply
angelo68mn
April 18, 2019 5:33 pm
I haven’t downloaded the database, but I guess the solution is something like above, where I introduce a Subquery which extract the average costs for ProductID from Production.Product
USE AdventureWorks2014
GO
SELECT pch.StandardCost, p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN
(SELECT ProductID, AVG() AS AVGStandardCost from Production.Product GROUP BY ProductID ) p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > AVGStandardCost
GO
Reply
Md Minhaj Ul Haq
April 18, 2019 5:34 pm
one more query with better performer
SELECT p.ProductID,PCH.StartDate,PCH.EndDate,pch.StandardCost,PCH.ModifiedDate
FROM Production.ProductCostHistory pch
INNER JOIN (SELECT AVG(S.StandardCost) StandardCost,s.productId FROM Production.Product S group by s.ProductID) p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.StandardCost
Reply
Akshay R
April 18, 2019 5:35 pm
Sir,
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 > (select AVG(StandardCost) from Production.ProductCostHistory where ProductID = p.ProductID)
and pch.StandardCost p.StandardCost
Reply
JK
April 18, 2019 5:35 pm
Here it goes, Pinal:
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
Reply