SQLTeam.com | Weblogs | Forums

I am supposed to have 25 rows, I have 0

List all products in the accessories category that have a list price lower than the average list price
for an accessory. Show product alternate key, English product name, and list price.
Order descending by list price. (10 points)

SELECT p.[ProductAlternateKey],p.[EnglishProductName],p.[ListPrice]
FROM [dbo].[DimProduct] AS p
WHERE p.ProductSubCategoryKey IN (4)AND p.[ListPrice] < (SELECT AVG([ListPrice])
FROM [dbo].[DimProduct]
WHERE ListPrice < 34.2281
ORDER BY ListPrice DESC

please start thinking what could be the reason !!!

is there data in the table ???

if there is data then
is there something wrong with SQL

or maybe the SQL is correct
but there is some issue in the data !!!

Most important of all
check your thinking !!!
you think you are supposed to have 25 rows
is that correct

how do you check your SQL ..
is 4 you are using the right value ???
etc etc etc ....
SELECT p.[ProductAlternateKey],p.[EnglishProductName],p.[ListPrice]
FROM [dbo].[DimProduct] AS p
WHERE p.ProductSubCategoryKey IN (4)AND p.[ListPrice] < (SELECT AVG([ListPrice])
FROM [dbo].[DimProduct]
WHERE ListPrice < 34.2281
ORDER BY ListPrice DESC

hope this helps :slight_smile:

You start a subquery, "(SELECT AVG(ListPrice)", but you don't finish it -- there's no closing paren, ).

Try this:

SELECT p.[ProductAlternateKey],p.[EnglishProductName],p.[ListPrice]
FROM [dbo].[DimProduct] AS p
WHERE p.ProductSubCategoryKey IN (4)AND p.[ListPrice] < (SELECT AVG([ListPrice])
FROM [dbo].[DimProduct])
ORDER BY ListPrice DESC
1 Like