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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Tom Lane Sent: Thursday, November 30, 2000 3:39 PM To: Gordan Bobic Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Built in Functions use with recordsets "Gordan Bobic" <[EMAIL PROTECTED]> 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
