CODEWITHSUNDEEP
mysql
Chris Dargis
Chris Dargis

Reputation: 6043

MySQL Aggregate in WHERE clause?

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?

Upvotes: 3

Views: 6666

Answers (2)

Nesim Razon
Nesim Razon

Reputation: 9794

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

Upvotes: 11

user1727088
user1727088

Reputation: 159

when working with aggregates there's a seperate clause you need to use called 'HAVING' which goes after the group by but it appears the query you are trying to write is actually more complex than that.

what is it that you want to see in the results?

I suspect you need to do a where against a sub query with the average to get what I'm guessing you want back

Upvotes: 0

Related Questions