| 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
I’m awaiting..
I have AdvantureWorks2012, so I used it instead.
USE AdventureWorks2012
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
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
select pch.*
from Production.ProductCostHistory pch
inner join (select ProductID, StandardCost from Production.Product ) as p on pch.ProductID= p.ProductID
WHERE pch.StandardCost > 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(StandardCost)
FROM
Production.Product)
GO
Sorry new here, how do we submit our answer?
Just leave a comment!
Hello Pinal,
here is the query I consider should be:
—————————————————————
SELECT p.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost, p.ModifiedDate
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.StandardCost
—————————————————————
Thank you.
Ops.
A correction :-)
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
the AVG in the query makes no sense… just remove it and it will be ok
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
select * from [Production].[ProductCostHistory] pch
inner join [Production].[Product] p
on p.ProductID=pch. [ProductID]
where pch.[StandardCost] >p.[StandardCost]
SELECT pch.StandardCost, p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN
(select
AVGCost = avg(standardcost) over(partition by productid order by productid),
productid
from Production.Product) p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.AVGCost
that can be solved only removing text from the query hahaha.. i’ve posted before, did you received?
There are over 416 comments in waiting… I am going through them.
ow… sorry, i did not received a confirmation after saving… thanks!
Did I get it?
Willer will be announcing a bit later… :-)
Currently no one is selected.
When the final counts are in please post the count of correct answers compared to the count of all of the answers submitted. It will be a confidence boost for me as I haven’t been working with SQL all that long and I know I submitted the code that produced the correct result set ;-)
Isn’t that a great feeling!
SELECT p.ProductID,pch.StandardCost,StartDate, EndDate, pch.ModifiedDate
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
–WHERE pch.StandardCost > AVG(p.StandardCost)
Group by p.ProductID, pch.ProductID, pch.StandardCost
,pch.StartDate, pch.EndDate, pch.ModifiedDate
Having pch.StandardCost > AVG(p.StandardCost)
Pinal,
Here is my solution. I tend towards using CTEs in most of my solutions:
USE AdventureWorks2014
GO
WITH cteAvg(ProductID, StandardCost, AvgStandardCost, StartDate, EndDate, ModifiedDate) AS (
SELECT p.ProductID, pch.StandardCost, AVG(p.StandardCost) OVER (PARTITION BY p.ProductID),
pch.StartDate, pch.EndDate, pch.ModifiedDate
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
)
SELECT ProductID, StartDate, EndDate, StandardCost, ModifiedDate
FROM cteAvg
WHERE StandardCost > AvgStandardCost
GO
SELECT pch.StandardCost, pch.ProductID
FROM Production.ProductCostHistory pch
CROSS APPLY(SELECT AVG(p.StandardCost) AS AverageStandardCost
FROM Production.Product p
WHERE pch.ProductID = p.ProductID) AS ProductAverages
WHERE pch.StandardCost > ProductAverages.AverageStandardCost
Oh crud, I see now… Remove “AVG(” & “)” as in
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
How much more time Pinal??it is now over 1am in India…I completed my shift 3 hours back and waiting for winner announcement
When you wake up tomorrow – the winner will have already email out :-)
Good luck guys. I’m from South Africa, so most likely won’t be online when winners announced due to time zones.
USE AdventureWorks2014
GO
SELECT pcha.ProductID, pch.StartDate,pch.EndDate, pch.StandardCost, pch.ModifiedDate
FROM Production.ProductCostHistory pch
CROSS APPLY(SELECT ProductID, AVG(StandardCost) AS AvgStandardCost FROM Production.Product
WHERE ProductID = pch.ProductID Group BY ProductID) AS pcha
WHERE pch.ProductID = pcha.ProductID AND pch.StandardCost > pcha.AvgStandardCost