SQL Trying To Find The Average of Just 1 Attribute

Question

In SQL, I am trying to find and list the addresses of what houses have a greater rent than the average of the houses rent. So I have a PropertyForRent table which lists different houses and flats for rent. Heres that table:

relation

So far, I have wrote the complete SQL Query but I'm not sure if it actually does what I intend it to do.

Here it is:

SELECT street, city, postcode, type, rent – (SELECT AVG (rent) FROM PropertyForRent WHERE type = ‘House’) AS avgRent
FROM PropertyForRent
WHERE rent >
    (SELECT AVG(rent)
    FROM PropertyForRent);

Is this correct syntax? Can I use a WHERE within a SELECT AVG? Or is there a better way to define the rent being specifically for houses? I think the rest of it is okay but I'm not entirely sure.

Thanks for any help

Answer 1

You can use a subquery in the where clause. I think, however, that you need to filter the dataset on Houses, like so:

select p.*
from PropertyForRent p
where 
    p.type = 'House'
    and p.rent > (select avg(p1.rent) from PropertyForRent p1 where p1.type = 'Rent')

This might be simpler expressed with window functions:

select *
from (
    select p.*, avg(rent) over() avg_rent
    from propertyForRent p
    where type = 'House'
) p
where rent > avg_rent