Re: Grouping aggregate functions

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.