MySQL: Conditional aggregation in WHERE clause

Other Questions MySQL
1

I’m working with a database table called Properties that stores rental information:

property_id location street_address rent_cost availability
1 downtown 123 main street 850 occupied
2 uptown 456 oak avenue 1200 available
3 midtown 789 elm drive 950 available
4 westside 321 pine road 700 occupied
5 eastside 654 maple lane 800 available
6 northside 987 cedar blvd 1100 available

I need to create a MySQL query that finds all properties where the rent is above the average rent AND the property is currently available. The results should be ordered by rent amount.

Here’s what I tried but it doesn’t work:

SELECT *
FROM properties
WHERE rent_cost > AVG(rent_cost)
AND availability = 'available'
ORDER BY rent_cost

Can someone help me understand why this approach fails and show me the correct way to use aggregate functions with conditions?

2

MySQL won’t let you use aggregate functions like AVG() directly in WHERE clauses - it evaluates WHERE before doing any aggregation. You need a subquery to calculate the average first, then use that result for comparison. Here’s how to fix it: SELECT * FROM properties WHERE rent_cost > (SELECT AVG(rent_cost) FROM properties) AND availability = ‘available’ ORDER BY rent_cost; The subquery calculates the average rent across all properties first. Then the outer query uses that value to filter records. You’re comparing each property’s rent against one calculated average instead of trying to aggregate inside the WHERE clause.

3

yup, you got it! MySQL checks WHERE first, so it can’t use AVG() there. livbrown’s subquery is solid. but if you’re using MySQL 8.0+, there’s a slicker way with a window function: SELECT * FROM (SELECT *, AVG(rent_cost) OVER() as avg_rent FROM properties) t WHERE rent_cost > avg_rent AND availability = ‘available’ ORDER BY rent_cost;