average/stddev on all values returned by a select distinct

From: ann hedley <ann(dot)hedley(at)ed(dot)ac(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: average/stddev on all values returned by a select distinct
Date: 2007-07-17 12:51:21
Message-ID: 469CBB49.8020905@ed.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Fuhr 2007-07-17 13:51:00 Re: average/stddev on all values returned by a select distinct
Previous Message Michael Nolan 2007-07-17 12:48:11 Re: What's the logical counterpart of the to_hex function?