SQL PLUS-Displaying Tables

0
votes

I need to write a query to display the brand ID, brand name, brand type, and average price of products for the brand that has the largest average product price

I can get the avg price of all brands by entering:

SQL> SELECT LGPRODUCT.BRAND_ID, BRAND_NAME, BRAND_TYPE,    ROUND(AVG(PROD_PRICE),2)
FROM BOFF.LGPRODUCT, BOFF.LGBRAND
WHERE LGPRODUCT.BRAND_ID = LGBRAND.BRAND_ID
GROUP BY LGPRODUCT.BRAND_ID, BRAND_NAME, BRAND_TYPE
ORDER BY LGPRODUCT.BRAND_ID;

but if I enter ROUND(MAX(AVG(PROD_PRICE)),2) I get an error, any suggestions?

1
sqloracle

1 Answers

0
votes

You can use window function rank to mark the ranks according the average and then filter to get the top ranked rows:

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

If you must use having then try:

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