display only 1 column from the subquery : list the bran_name whos avg price is >= 3 and also have at least 2 distinct products

Question

Here is my query:

select avg(p2.price), p2.brand_name, count(distinct p2.product_id) 
count_of_products
from product p2
group by p2.brand_name
having count(distinct p2.product_id) >= 2 and avg(p2.price) > 3

but I need to display only band_name in the final query. I tried this:

select p1.brand_name from product p1,
(select avg(p2.price), p2.brand_name, count(distinct p2.product_id) 
count_of_products
from product p2
group by p2.brand_name
having count(distinct p2.product_id) >= 2 ) p2 and and avg(p2.price) > 3
where 
p1.brand_name = p2.brand_name

but it gives me following error:

>[Error] Script lines: 3-9 --------------------------
 No column name was specified for column 1 of 'p2'.

Answer 1

but I need to display only band_name in the final query

So just remove the other columns from the select clause:

select brand_name
from product
group by brand_name
having count(distinct product_id) >= 2 and avg(price) > 3