Thread: average/stddev on all values returned by a select distinct

I have this query which returns the length of the longest consensus for
each id.

lumbribase=# select distinct on (id) length(consensus) from cluster
order by id,length(consensus) desc;
 length
--------
    647
    680
    273
    384
   1285
<snip>

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);

Could someone tell me how it's done?

Thanks

--
Ann

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

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;

--
Michael Fuhr

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)