CODEWITHSUNDEEP
mysqlsql
Matthew Brousseau
Matthew Brousseau

Reputation: 3

Having trouble finding maximum average in MySQL query

I'm having an issue with an SQL query.

Essentially, I am trying to find the maximum average value for a group.

This is what I've written:

SELECT *
FROM (SELECT BRAND_NAME AS AVG_BRAND, AVG(PROD_PRICE) 
AS AVG_PRICE FROM LGPRODUCT AS PROD, LGBRAND AS BRAND 
WHERE PROD.BRAND_ID=BRAND.BRAND_ID GROUP BY PROD.BRAND_ID) AS AVG_GROUP
WHERE AVG_PRICE=MAX(AVG_PRICE);

This is what the subquery returns:

This is what the subquery returns

I can retrieve AVG_PRICE and MAX(AVG_PRICE). I can even do WHERE statements such as:

WHERE AVG_PRICE>20

And that works. When I run my query though I get an error code 1111.

Any help would be greatly appreciated.

Thanks.

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Are you looking for something like this?

SELECT BRAND_NAME AS AVG_BRAND, AVG(PROD_PRICE) AS AVG_PRICE
FROM LGPRODUCT PROD JOIN
     LGBRAND BRAND 
     ON PROD.BRAND_ID = BRAND.BRAND_ID
GROUP BY PROD.BRAND_ID
ORDER BY AVG_PRICE DESC
LIMIT 1;

Upvotes: 1

Related Questions