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@xxxxxxxxx> 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.
