oracle - SQL PLUS-Displaying Tables -

i need write query display brand id, brand name, brand type, , average price of products brand has largest average product price

i can avg price of brands entering:

sql> select lgproduct.brand_id, brand_name, brand_type,    round(avg(prod_price),2) boff.lgproduct, boff.lgbrand lgproduct.brand_id = lgbrand.brand_id group lgproduct.brand_id, brand_name, brand_type order lgproduct.brand_id; 

but if enter round(max(avg(prod_price)),2) error, suggestions?

you can use window function rank mark ranks according average , filter top ranked rows:

select * (     select lgproduct.brand_id,         brand_name,         brand_type,         round(avg(prod_price), 2),         rank() on (             order round(avg(prod_price), 2) desc             ) rnk     boff.lgproduct,         boff.lgbrand     lgproduct.brand_id = lgbrand.brand_id     group lgproduct.brand_id,         brand_name,         brand_type     ) t rnk = 1 order brand_id; 

if must use having try:

select lgproduct.brand_id,     brand_name,     brand_type,     round(avg(prod_price), 2) boff.lgproduct,     boff.lgbrand lgproduct.brand_id = lgbrand.brand_id group lgproduct.brand_id,     brand_name,     brand_type having round(avg(prod_price), 2) = (         select max(round(avg(prod_price), 2))         boff.lgproduct,             boff.lgbrand         lgproduct.brand_id = lgbrand.brand_id         group lgproduct.brand_id,             brand_name,             brand_type         ) order lgproduct.brand_id;