Getting error in MySQL as Error Code: 1054. Unknown column 'Price' in 'having clause'

Question

I have created a table as follows:

create table products 
(productID int not null auto_increment,
 Name varchar(30),
 Price float ,
 CoffeOrigin varchar(30),
 primary key (productID));

Now the question is "Display the name of products whose price is less than average price."

My 1st attempt:

select Name from products having Price <= avg(Price);

It is giving me error as Error Code: 1054. Unknown column 'Price' in 'having clause'

My 2nd attempt:

select * from products having Price <= avg(Price);

It is giving me incomplete output. Here average Price is 3.14 but is only products with price less than 3 are being displayed in the result.

Answer 1

You want to compare the price on each row to the average price over the whole table, so you can't just use aggregation. Both your attempts are not valid aggregation queries.

If you are running MySQL 8.0, you can just use window functions:

select p.*
from (select p.*, avg(price) over() avg_price from products p) p
where price <= avg_price

In earlier versions, one option uses a scalar subquery to compute the average:

select p.*
from products p
where price <= (select avg(price) from products)