RE: Built in Functions use with recordsets

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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
Sent: Thursday, November 30, 2000 3:39 PM
To: Gordan Bobic
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Built in Functions use with recordsets

"Gordan Bobic" <gordan(at)freeuk(dot)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