Give the table “shop”
[product] [type] [price] pen A 10 glasses B 20 lipstick A 30
Is there a way to find the list of products(whether A or B) whose price is less or equal to the average price or type A?
[product] [price] pen 10 glasses 20
I tried the following:
SELECT product, price FROM shop WHERE price <= avg(price) AND type = 'A'
Advertisement
Answer
Use a subquery to find the average price of type A items:
SELECT product, price FROM shop WHERE price <= (SELECT AVG(price) FROM shop WHERE type = 'A');
Just for fun, we could also use analytic functions here:
WITH cte AS ( SELECT *, AVG(price) FILTER (WHERE type = 'A') OVER () avg_price_a FROM shop ) SELECT product, price FROM cte WHERE price <= avg_price_a;