Re: Grouping aggregate functions

Thanks! That did the trick.

> SELECT avg(minprice) FROM
> (SELECT min(price) as minprice FROM weekly_supply_prices GROUP BY
> month );

This came up with an error too, but it pointed me in the right
direction and was easy to fix. I needed to use an alias for the
entire subquery too, so what finally worked is this:

SELECT avg(minprice) FROM
(SELECT min(price) AS minprice FROM weekly_supply_prices GROUP BY
month) AS minprice_table;

Thanks again,
Rich

On Apr 2, 2006, at 4:51 AM, Martijn van Oosterhout wrote:

> On Sun, Apr 02, 2006 at 04:03:03AM -0700, Richard Connamacher wrote:
>> I've got a question, if anyone can help me out. I know how to use an
>> aggregate function to, say, find the lowest price ever listed for a
>> product. I also know how to combine that with a SELECT ... GROUP BY
>> statement to find, say, the lowest price reported for each month.
>> Now, what if I want to find the *average* of all the lowest prices
>> for each month? Plopping that SELECT statement inside parentheses and
>> inside an "avg( )" function produces an error.
>
> Use a subquery. ie.e not:
>
>> SELECT avg( ( SELECT min(price) FROM weekly_supply_prices GROUP BY
>> month ) )
>
> But
>
> SELECT avg(minprice) FROM
> (SELECT min(price) as minprice FROM weekly_supply_prices GROUP BY
> month );
>
>> Anyone have any idea how to do this? Or do I have to compute the
>> average in another program?
>
> Use SQL to calculate both :) One way to think about it is by think of
> the subquery producing a temporary table which you then use in another
> query.
>
> Have a nice day,
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/
> kleptog/
>> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent
>> is a
>> tool for doing 5% of the work and then sitting around waiting for
>> someone
>> else to do the other 95% so you can sue them.