Re: [GENERAL] average/stddev on all values returned by a select distinct

[ann hedley]

Michael Fuhr wrote:

On Tue, Jul 17, 2007 at 01:51:21PM +0100, ann hedley wrote:

What I want is the average and stddev of the set of lengths returned by this query. Something like...

select average(select distinct on (id) length(consensus) from cluster order by id,length(consensus) desc);

I think this is what you're looking for:

select avg(length)
  from (
    select distinct on (id) length(consensus)
      from cluster
     order by id, length(consensus) desc
  ) s;

Thank you, it was indeed.

--
Ann

"In a world without walls and fences - who needs Windows and Gates ?"
                                                          (unknown)


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend