Обсуждение: Grouping aggregate functions

Hey all,

I'm new on this list, and have been playing with Postgres a lot this
week. (Love it, by the way.)

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.

What I'd love to do, and which creates an error, would be something
like:

SELECT avg( ( SELECT min(price) FROM weekly_supply_prices GROUP BY
month ) )

The error I get is: "ERROR:  more than one row returned by a subquery
used as an expression" (to state the obvious). If I don't double up
the quotes I get a syntax error.

Anyone have any idea how to do this? Or do I have to compute the
average in another program?

Thanks!
Richard

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

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

Richard Connamacher wrote:
> 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;

Interesting, but you're right ;) Btw. if you want you may skip the 'AS'.
Just 'SELECT ... FROM (....) mt;