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.
Attachment:
signature.asc
Description: Digital signature
