The following query: SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products) selects all products with a above-average prices.
Is it possible to do this using aggregate functions somehow instead of a subquery (nested SELECT) or a Common Table Expression (WITH).
I am asking primarily about Postgres but MySQL or SQL Server also helps.
Something like SELECT * FROM products HAVING price > AVG(price), which doesn't work.
I think the answer is no.
And there is one additional line because the answer must be at least 30 characters.
We can achieve these using inner join. Check below query.
SELECT p1.* FROM products P1
inner join (SELECT AVG(price)as price FROM products) P2 on P1.price > P2.price
The derived table for this solution is only syntactic sugar to be able to apply a where condition on the column alias:
select *
from (
select p.*,
avg(price) over() as avg_price
from products
) t
where price > avg_price;