CODEWITHSUNDEEP
sqldatabasems-access
Mina
Mina

Reputation: 315

MAX and MIN average price in different product categories using GROUP BY and HAVING [SQL]

In Microsoft Access, Northwind database, I have to show min and max of avg UnitPrice in different product categories using GROUP BY and HAVING. Here's what I tried:

SELECT MAX(SELECT AVG(UnitPrice) FROM Products) AS MaxSV, MIN(SELECT AVG(UnitPrice) FROM Products) AS 
MinSV FROM Products GROUP BY CategoryID, UnitPrice HAVING DISTINCT CategoryID;

Second thing I have to do is show average of max UnitPrice by CategoryID, also using GROUP BY and HAVING.

Upvotes: 4

Views: 885

Answers (1)

Punker
Punker

Reputation: 1878

Try this:

SELECT MAX(t1.avg_price), MIN(t1.avg_price) 
FROM (SELECT AVG(UnitPrice) AS avg_price
      FROM Products 
      GROUP BY CategoryID) AS t1

Upvotes: 3

Related Questions