MySQL Aggregate in WHERE clause?

Question

I have the following database schema:

Customer(ssn, name, gender, city)
Vehicle(vin, make, model, year)
BuyVehicle(bvssn, bvvin, price, year)

Where BuyVehicle.bvvin is a foreign key from Vehicle.vin and BuyVehicle.bvssn is a foreign key from Customer.ssn. I am trying to select names of the people who purchased vehicles at a greater price than the average of that specific vehicle.

So far, I have determined how to compute the average of the vehicles:

SELECT AVG(price) as avg_price, v.maker, v.model, v.year FROM BuyVehicle bv, Vehicle v, Customer c WHERE v.vin = bv.BVVIN AND c.ssn = bv.bvssn GROUP BY maker, model, year;

I have tried to enter a price > avg_price condition in the WHERE clause:

SELECT AVG(price) as avg_price, v.maker, v.model, v.year FROM BuyVehicle bv, Vehicle v, Customer c WHERE v.vin = bv.BVVIN AND c.ssn = bv.bvssn AND bv.price > avg_price GROUP BY maker, model, year;

but MySQL tells me that the avg_price column does not exist. Am I taking the wrong approach to this problem?

Answer 1

you need to use having:

SELECT AVG(price) as avg_price, v.maker, v.model, v.year, bv.price 
FROM BuyVehicle bv, Vehicle v, Customer c WHERE v.vin = bv.BVVIN AND c.ssn = bv.bvssn
GROUP BY maker, model, year
having bv.price > avg_price