can't understand subquery code using where and group by

user_12335443 0
Viewed 65

if I wanna compare sale price of each product type with the sale price of each item within corresponding product type, here is the code to get it:

SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE sale_price > (SELECT AVG(sale_price) 
                    FROM Product AS P2
                    WHERE P1.product_type = P2.product_type
                    GROUP BY product_type );

I can't understand subquery code for 'where P1.product_type = P2.product_type'. For the WHERE outside the subquery, it should be used to filter single row. However, how could it get a single result in subquery?

subquery where-clause group-by postgresql sql
2 Answers

The query you wanted to write:

SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE sale_price > (
    SELECT AVG(sale_price) 
    FROM Product AS P2
    WHERE P1.product_type = P2.product_type
);

This phrases as: bring all rows from product whose sale_price is greater than the average sale_price of products of the same type. The subquery in the where clause is called a correlated subquery, and computes the average for all rows having the same product_type (the where clause of the subquery implements the correlation).

This could also be expressed with window functions:

select *
from (
    select  
        p.*,
        avg(sale_price) over(partition by product_type) avg_price_by_product_type
    from product p
) t
where sale_price > avg_price_by_product_type
user_106767160

You're running a AVG(), is a grouping function. This means that you get the average value for all values returned by the subquery. As you not selecting other columns, the group assumes that you group all sales_price rows.

And, the RDBMS knows that you have a table called P1 from the main query and, for every line returned by the main select, the RDBMS executes the subquery comparing the result in P1 comparing to P2 query.

user_121216270
Related