thx for anwering!, i was more looking for a percentile calculation as in http://en.wikipedia.org/wiki/Percentile usually i do this in perl with the 'Statistics::Descriptive' module, but it would help me alot if i could solve this within the sql query, and would not have to push all the data into a perl arrays first.. so i could e.g. simply say: select 95percentile(column with many many values) from sometable actually what i really need is something like: select 95p(col), 98p(col), max(col), min(col), avg(col) from thetable... that would save me alot of cycles i'm takin in perl at the moment... guess my sql is not smart enough, but was thinking a user defined function would be of great use :-) any more suggestions? tia! -k ----- Original Message ---- From: A. Kretschmer <andreas.kretschmer@xxxxxxxxxxxxxx> To: pgsql-general@xxxxxxxxxxxxxx Sent: Monday, April 6, 2009 4:53:28 AM Subject: Re: user defined aggregate for percentile calculations In response to Kashmir : > > cant seem to find anything about this, > would it possible at all to create a percentile-aggregate in pgres? > any pointers? > tia > -k I'm not sure if i understand your problem, but how about: test=*# select * from percentile ; id | value ----+------- 1 | 10 2 | 20 3 | 30 4 | 50 (4 rows) test=*# select a.id, a.value, (a.value*100/foo.summe)::numeric(10,2) from percentile a, (select sum(value) as summe from percentile ) as foo group by a.id, a.value, foo.summe order by id; id | value | numeric ----+-------+--------- 1 | 10 | 9.00 2 | 20 | 18.00 3 | 30 | 27.00 4 | 50 | 45.00 (4 rows) HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
