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

khushal3012
April 19, 2019 10:41 am
1.
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.VN_OP_Bal, p.VN_Sech_Id
having pch.StandardCost > AVG(p.StandardCost)
2.
USE AdventureWorks2014
GO
SELECT pch.StandardCost, p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN (Select ProductID,AVG(p.StandardCost)StandardCost from Production.Product
Group by ProductID) as p ON pch.ProductID = p.ProductID
Where pch.StandardCost > p.StandardCost
Reply
subodh kamble
April 19, 2019 10:51 am
USE AdventureWorks2014
GO
select *
from
(
SELECT pch.StandardCost, p.ProductID , AVG(p.StandardCost) over (order by p.StandardCost row between unbounded preceding and current row) as avg_StandardCost
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
)temp
WHERE StandardCost > avg_StandardCost
GO
Reply
BD Softley
April 19, 2019 11:21 am
SELECT pch.ProductID
,pch.StartDate
,pch.EndDate
,pch.StandardCost
,pch.ModifiedDate
FROM Production.ProductCostHistory pch
INNER JOIN (select ProductID , avg(StandardCost) ‘avg_StandardCost’
from Production.Product p
Group by ProductID) p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.avg_StandardCost
ProductID StartDate EndDate StandardCost ModifiedDate
———– ———————– ———————– ——————— ———————–
707 2012-05-30 00:00:00.000 2013-05-29 00:00:00.000 13.8782 2013-05-29 00:00:00.000
708 2012-05-30 00:00:00.000 2013-05-29 00:00:00.000 13.8782 2013-05-29 00:00:00.000
711 2012-05-30 00:00:00.000 2013-05-29 00:00:00.000 13.8782 2013-05-29 00:00:00.000
858 2012-05-30 00:00:00.000 2013-05-29 00:00:00.000 9.7136 2013-05-29 00:00:00.000
859 2012-05-30 00:00:00.000 2013-05-29 00:00:00.000 9.7136 2013-05-29 00:00:00.000
860 2012-05-30 00:00:00.000 2013-05-29 00:00:00.000 9.7136 2013-05-29 00:00:00.000
(6 rows affected)
Reply
sagar patel
April 19, 2019 11:46 am
Hello Pinal Sir,
First of all I checked that there is single record in “Production.Product” table per ProductID. Hence, we do not require AVG function in where clause. Please see below result screen shot.
USE AdventureWorks2014
GO
SELECT pch.*
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.StandardCost
GO
If we require to do AVG then please check below query which also gives same result.
USE AdventureWorks2014
GO
;WITH Product
AS
(SELECT p.ProductID,AVG(p.StandardCost) AS StandardCost
FROM Production.Product p
GROUP BY p.ProductID
)
SELECT pch.*
FROM Production.ProductCostHistory pch
INNER JOIN Product p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.StandardCost
GO
Please let me know if I am right or not. Waiting for your reply.
Reply
Yogesh Shinde
April 19, 2019 11:49 am
Q1. Do you want exact 6 records as output?
Q.2 Avg of P.StandardCost does that mean average of all products?
Reply
sagar patel
April 19, 2019 11:57 am
Hello Pinal Sir,
First of all, I checked that there is single record in “Productio.Product” table per ProductID. Hence, AVG function is not required in where clause. If you run below query then you will get expected result.
USE AdventureWorks2014
GO
SELECT pch.*
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.StandardCost
GO
Second, if we still require to do AVG then below query also gives same result.
USE AdventureWorks2014
GO
;WITH Product
AS
(SELECT p.ProductID,AVG(p.StandardCost) AS StandardCost
FROM Production.Product p
GROUP BY p.ProductID
)
SELECT pch.*
FROM Production.ProductCostHistory pch
INNER JOIN Product p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.StandardCost
GO
Please let me know if I am right or not.
Waiting for your reply.
Reply
Marco
April 19, 2019 12:07 pm
Hi Pinal,
I Post my answer but I have a question:
This query is strange: the AVG of StandardCost table Production.Product is neeed to be an average af ALL products or by product?
Because it’s strange an AVG of all the products but also an AVG of a product (AVG of 1 row?!?!?)
I hope to be able to explain….
Reply
- Pinal Dave
  April 23, 2019 9:44 am
  Great point.
  Reply
vasu
April 19, 2019 12:26 pm
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)
group by p.ProductID ,pch.StandardCost
having pch.StandardCost > AVG(p.StandardCost)
GO
Reply
vasu
April 19, 2019 12:29 pm
Hi Pinal, Am I posting the query in the right place if not please let me know where I need to post it. Please let me know your comments.
Reply
- Pinal Dave
  April 23, 2019 9:44 am
  This is the right place.
  Reply
Priyanka
April 19, 2019 12:30 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
where ProductID = p.ProductID)
Reply
sd
April 19, 2019 12:48 pm
–Well, just reposting the same answer:
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 pch.ProductID = p.ProductID)
;
–or using CTE
with _avg as(select ProductID, AVG(p.StandardCost) avgsc from Production.Product p group by ProductID)
select pch.ProductID, pch.startdate, pch.enddate, pch.StandardCost, pch.ModifiedDate
FROM Production.ProductCostHistory pch, _avg
WHERE pch.StandardCost > _avg.avgsc and pch.ProductID=_avg.ProductID
Reply
sd
April 19, 2019 1:02 pm
–one more method:
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)
GO
:)
Reply
Shrijith Gopalakrishnan
April 19, 2019 1:07 pm
— To check if Product has had greater Price in past compared to current price.
— If such record exists, fetch details of that record from ProductCostHistory
;WITH tblProductHistory AS
(SELECT a.ProductId,’Max Price Entry’ AS [type],a.StandardCost,a.StartDate,a.EndDate,a.ModifiedDate
FROM Production.ProductCostHistory a LEFT OUTER JOIN
Production.ProductCostHistory b ON a.ProductID = b.ProductID
AND a.StandardCost < b.StandardCost
WHERE b.ProductID IS NULL
UNION
SELECT ProductId,’Current Price Entry’,StandardCost,NULL,NULL,NULL
FROM Production.ProductCostHistory
WHERE EndDate IS NULL)
SELECT tblA.ProductId,tblA.StartDate,tblA.EndDate,tblA.StandardCost,tblA.ModifiedDate
FROM tblProductHistory tblA INNER JOIN
tblProductHistory tblB ON tblA.ProductID=tblB.ProductID
AND tblB.[type] = ‘Current Price Entry’
WHERE tblA.EndDate is not null AND tblA.StandardCost>tblB.StandardCost
Reply
Sandeep
April 19, 2019 2:12 pm
SELECT pch.StandardCost, p.ProductID
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
Group BY p.StandardCost,p.ProductID
HAVING pch.StandardCost > AVG(p.StandardCost)
GO
Reply
balajijayaraman
April 19, 2019 2:12 pm
Hi Pinal,
Here is the code that produce correct result,
USE AdventureWorks2014
GO
SELECT p.ProductID
,pch.StartDate
,pch.EndDate
,pch.StandardCost as StandardCost
,pch.ModifiedDate
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > (p.StandardCost)
Reply
Mayura
April 19, 2019 3:00 pm
Please ignore my previous post. Consider this query
USE AdventureWorks2014
GO
SELECT pch.*
FROM Production.ProductCostHistory pch
INNER JOIN Production.Product p ON pch.ProductID = p.ProductID
WHERE pch.StandardCost > p.StandardCost
GO
Removed AVG function as it is not required. Clustered, unique, primary key is exist on column ProductID for Production.Product table, hence no chances of having multiple entries and not required to apply AVG function.
If you are looking avg from the history data, then consider my previous query.
Reply
Ajay Dwivedi
April 19, 2019 3:50 pm
USE AdventureWorks
GO
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 pch.ProductID = p.ProductID)
GO
Reply
Anshul Singh
April 19, 2019 4:02 pm
With cte
as
(
SELECT p.ProductID, p.SellEndDate ‘StartDate’, p.SellEndDate ‘EndDate’,pch.StandardCost ‘StandardCost’, p.ModifiedDate ‘ModifiedDate’, p.StandardCost ‘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 StandardCost > (SELECT AVG(p_StandardCost) FROM cte)
Reply
Ajay Dwivedi
April 19, 2019 4:09 pm
— Method 01 – Correlated Query
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 pch.ProductID = p.ProductID)
GO
— Method 02 – Apply Join Clause
SELECT pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost, pch.ModifiedDate
FROM Production.ProductCostHistory pch
CROSS APPLY
( select AVG(p.StandardCost) AS StandardCost from Production.Product p where pch.ProductID = p.ProductID ) as p
WHERE pch.StandardCost > p.StandardCost
GO
Reply
Bilal Rauf
April 19, 2019 4:10 pm
You can use sub query:
SELECT pch.StandardCost, p.ProductID
FROM Production.ProductCostHistory pch
WHERE pch.StandardCost > (select AVG(p.StandardCost) from Production.Product p where pch.ProductID = p.ProductID)
Reply