Grouping aggregate functions

From: Richard Connamacher <rich(dot)n1(at)indieimage(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Grouping aggregate functions
Date: 2006-04-02 11:03:03
Message-ID: 9ECBA3BE-1CDD-4C2F-A7F6-B4C1AB47D402@indieimage.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message fufay 2006-04-02 11:37:44 Re: installation problem - semaphores
Previous Message Tino Wildenhain 2006-04-02 10:57:50 Re: PostgreSQL makes me lie