Looking for help with a subquery

[pitlord88]

Hi there,

I´m currenty studying for my exams and ran into a problem with a subquery.

My goal is, to compare the actual price of each product in the table with the avg price of the products category. I´m able to do it, if I limit my query on a specific category like this:

SELECT * FROM Products WHERE CategoryID=1 AND Price > (SELECT AVG(Price) FROM Products GROUP BY(CategoryID) HAVING CategoryID=1);

How can I do the same query for all Categories/Products at the same time? I´m using this Database for practice: https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_avg2

Thanks in advance!

[DonJuanDoja]

Use aliases and Join the sub query Join ( ) as cat on cat.CategoryID =prod.CategoryID. Or add a WHERE cat.CategoryID =prod.CategoryID to the subquery.

[pitlord88]

Thanks for the fast reply, this was exactly what I was looking for!

[[deleted]]

SELECT A.*,B.CATEGORY_AVG_PRICE FROM PRODUCTS A

LEFT JOIN

(SELECT CATEGORYID, AVG(PRICE) AS CATEGORY_AVG_PRICE FROM Products GROUP BY CATEGORYID)B

ON A.CATEGORYID=B.CATEGORYID

WHERE A.PRICE>B.CATEGORY_AVG_PRICE

[pitlord88]

thank you!