0

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.

Martin
  • 16,093
  • 1
  • 29
  • 48
Matthew Brousseau
  • 3
  • 1
  • Use `HAVING` instead of `WHERE`. – Barmar Sep 14 '18 at 17:04
  • Simply moving that `WHERE` clause to a `HAVING` clause won't work here, although it will get you closer. Instead, take that subquery out and stick `ORDER BY AVG_PRICE DESC LIMIT 1` at the end of it and rerun (just the subquery) – JNevill Sep 14 '18 at 17:06
  • I don't think this is just a `HAVING` versus `WHERE` issue. If you notice, the solution I'm proposing has neither `HAVING` nor `WHERE`. – Gordon Linoff Sep 14 '18 at 20:24

1 Answers1

1

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786