Skip to content
Advertisement

SQL average of a subset of a column

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;

Demo

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement