Re: [GENERAL] avg() of array values
Martijn van Oosterhout wrote: > avg(*) is not valid, same for sum(*) Doh! Thanks. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] avg() of array values
On 9/11/07, Alban Hertroys <[EMAIL PROTECTED]> wrote: > I would have expected an avg of 2.0 and a sum of 4, where am I going wrong? This works for me: select avg(a) from explode_array(array[1, 3]) a; avg 2. (1 row) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] avg() of array values
On Tue, Sep 11, 2007 at 05:50:38PM +0200, Alban Hertroys wrote: > *> select avg(*) from explode_array(array[1, 3]); > avg > > 1. > (1 row) avg(*) is not valid, same for sum(*) the reaosn you get the answer you do it because postgres replaces the * with a 1, whic doesn't change the fact that the query is wrong. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[GENERAL] avg() of array values
Hi, I'm trying to get an avg value of 2 dates (to get to the month that most part of an interval is in). I found SP's to generate rows from array values, which I figured I could use with the avg aggregate, but to my surprise: *> create or replace function explode_array(in_array anyarray) returns setof anyelement as -> $$ $> $> select ($1)[s] from generate_series(1,array_upper($1, 1)) as s; $> $> $$ -> language sql immutable; CREATE FUNCTION *> select * from explode_array(array[1, 3]); explode_array --- 1 3 (2 rows) *> select avg(*) from explode_array(array[1, 3]); avg 1. (1 row) *> select sum(*) from explode_array(array[1, 3]); sum - 2 (1 row) I would have expected an avg of 2.0 and a sum of 4, where am I going wrong? Or is there a better way to get the avg of 2 dates (median would suffice, but I don't know the interval length in days before hand - and thus not the middle point of the interval). -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
