How to average the minimums of a group by-CodePudding

If I select the minimum of every group:

SELECT MIN(price) FROM Products GROUP BY category

How can I get the average of this minimums? I understand that

SELECT AVG(MIN(price)) FROM Products GROUP BY category

won't work. Any help here?

CodePudding user response：

You need to do this in two steps:

WITH min_prices as 
(
  SELECT category, MIN(price) as min_price 
  FROM Products 
  GROUP BY category
)
SELECT category, AVG(min_price) as avg_min_price
FROM min_prices
GROUP BY category

CodePudding user response：

You have to find minimums for all categories and then apply the average function to them.

select avg(m) as AvgOfAllMins from 
(
select category,min(price) as m from products
group by category) T;

See the result from db-fiddle.