How about
SELECT
SUM(CASE WHEN otherfield = 'criteria' THEN 1 ELSE 0 END) as count_comply,
SUM(CASE WHEN otherfield = 'criteria' THEN somefield ELSE 0 END) as
sum_comply,
sum_comply/count_comply AS mean_comply
FROM sometable
ORDER BY thirdfield
LIMIT 10
?
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Thursday, November 30, 2000 3:39 PM
To: Gordan Bobic
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Built in Functions use with recordsets
"Gordan Bobic" <gordan@freeuk.com> writes:
> over a certain set of records. In effect, I want to do something like:
> SELECT somefield
> FROM sometable
> WHERE otherfield = 'criteria'
> ORDER BY thirdfield
> LIMIT 10
> and then do an avg(somefield).
> Can this be done without using temp tables, in a single query?
Not if the order by/limit are essential to selecting the rows you need
to average over. At least not in 7.0 ... in 7.1 this'll work:
SELECT avg(somefield) FROM
(SELECT somefield FROM sometable ... LIMIT 10) t1;
For the moment a temp table is the way to go.
regards, tom lane