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)
527 Comments. Leave new
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
WHERE pch.EndDate = ‘2013-05-29’
AND p.StandardCost AVG(p.StandardCost) )
ORDER BY p.ProductID
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
) avgProd on avgProd.ProductID = pch.ProductID
WHERE pch.StandardCost > avgProd.AvgStandardCost
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(StandardCost) FROM Production.Product S WHERE P.ProductID = S.ProductID)
Final Query:
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)
We can get the exact result by including all the result columns into select and group by
#Solution One with correlated subquery
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(psub.StandardCost)
FROM Production.Product psub
WHERE pch.ProductID = psub.ProductID)
GO
#Solution 2 with avg window
USE AdventureWorks2014
GO
WITH psub ( ProductID
,StartDate
,EndDate
,StandardCost
,ModifiedDate
,avgStdCost
)
AS
(
SELECT
p.ProductID
,pch.StartDate
,pch.EndDate
,pch.StandardCost
,pch.ModifiedDate
,avg(p.StandardCost) OVER (PARTITION BY p.ProductID) as avgStdCost
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p
ON pch.ProductID = p.ProductID
)
SELECT
psub.ProductID
,psub.StartDate
,psub.EndDate
,psub.StandardCost
,psub.ModifiedDate
FROM psub
WHERE psub.StandardCost > psub.avgStdCost
GO
Sorry for 2nd Reply, The first one need to be considered please.
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
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)
USE AdventureWorks2014;
GO
SELECT [p].[ProductID]
, AVG([p].[StandardCost]) AS [AvgStandardCost]
INTO [#AvgProductStandardCost]
FROM [Production].[Product] AS [p]
GROUP BY [p].[ProductID];
SELECT [pch].[StandardCost]
, [p].[ProductID]
FROM [Production].[ProductCostHistory] AS [pch]
INNER JOIN [Production].[Product] AS [p] ON [pch].[ProductID] = [p].[ProductID]
INNER JOIN [#AvgProductStandardCost] AS [apsc] ON [apsc].[ProductID] = [p].[ProductId]
WHERE [pch].[StandardCost] > [apsc].[AvgStandardCost];
GO
— Drop Temp Tables
DROP TABLE #AvgProductStandardCost
USE AdventureWorks2017
GO
–SELECT [pch].[StandardCost], [p].[ProductID] — original task script
SELECT [p].[ProductID], [pch].[StartDate], [pch].[EndDate], [pch].[StandardCost], [pch].[ModifiedDate] — example
FROM [Production].[ProductCostHistory] AS [pch]
INNER JOIN [Production].[Product] AS [p] ON [pch].[ProductID] = [p].[ProductID]
CROSS APPLY (
SELECT [p2].[ProductID], AVG([p2].[StandardCost]) AS [AVGStandardCost]
FROM [Production].[Product] AS [p2]
WHERE [p2].[ProductID] = [p].[ProductID]
GROUP BY [p2].[ProductID]
) AS [SubAVG]
WHERE [pch].[StandardCost] > [SubAVG].[AVGStandardCost]
ORDER BY [p].[ProductID]
GO
Here is the correct query.
SELECT p.ProductID, pch.StartDate, pch.EndDate,pch.StandardCost,pch.ModifiedDate
FROM Production.ProductCostHistory pch
INNER JOIN (SELECT ProductID,AVG(StandardCost)StandardCost FROM Production.Product GROUP BY ProductID) P ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > P.StandardCost
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) AS AvgStandardCost FROM Production.ProductCostHistory)
Here is the correct query –
SELECT p.ProductID, pch.StartDate, pch.EndDate,pch.StandardCost,pch.ModifiedDate
FROM Production.ProductCostHistory pch
INNER JOIN (SELECT ProductID,AVG(StandardCost)StandardCost FROM Production.Product GROUP BY ProductID) P ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > P.StandardCost
GO
SELECT p.ProductID, StartDate, EndDate,pch.StandardCost,p.ModifiedDate
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
inner join (SELECT productid,AVG(StandardCost) AvgCost FROM Production.Product group by productid) AvgProd on AvgProd.productid=p.productid
WHERE pch.StandardCost > AvgProd.AvgCost
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 pch.StandardCost, p.ProductID, pch.StartDate, pch.EndDate, pch.ModifiedDate
HAVING pch.StandardCost > AVG(p.StandardCost)
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(p.StandardCost) FROM Production.Product p WHERE pch.ProductID = p.ProductID)
Well I don’t have the sample db but looking at the problem and error statement it’s clear. Please find my solution:-
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(p.StandardCost) FROM Production.Product p WHERE pch.ProductID = p.ProductID)
Pinal Dave,
I believe we have to use group by & HAVING Count(*)>StandardCost.
Here’s my answer, which returns the same result set as you have published:
SELECT
p.ProductID,
pch.StartDate,
pch.EndDate,
pch.ModifiedDate,
pch.StandardCost
FROM
Production.ProductCostHistory pch
INNER JOIN Production.Product p
ON pch.ProductID = p.ProductID
WHERE
pch.StandardCost > (SELECT AVG(pAVG.StandardCost) FROM Production.Product pAVG WHERE pAVG.ProductID = p.ProductID);
;with cte_AVG as
(
SELECT p.ProductID
,AVG(p.StandardCost) AG
FROM Production.Product p
group by p.ProductID
)
SELECT pch.ProductID
,pch.StartDate
,pch.EndDate
,pch.StandardCost
,pch.ModifiedDate
FROM Production.ProductCostHistory pch
INNER JOIN cte_AVG p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.AG
Hi Pinal,
How can i ans. the puzzle. Am i need email you, or leave the ans. in comment box.
Ans. is
USE AdventureWorks2014
GO
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)
GO
I want to attend your class.I don’t have that budget so i want to win this puzzle. :)