Thread: Aggregate Functions

Yadnyesh Joshi <yadnyesh_joshi@yahoo.com> writes:
> According to the documentation, when "avg" or "stddev" function is used wit=
> h integer arguments, the result is an integer.

Where do you think you read that?

regression=# create table foo(f1 int);
CREATE TABLE
regression=# insert into foo values(1);
INSERT 0 1
regression=# insert into foo values(2);
INSERT 0 1
regression=# insert into foo values(7);
INSERT 0 1
regression=# select avg(f1) from foo;
        avg
--------------------
 3.3333333333333333
(1 row)

regression=# select stddev(f1) from foo;
       stddev
--------------------
 3.2145502536643183
(1 row)

regression=#

            regards, tom lane

> According to the documentation, when "avg" or "stddev" function is used with integer arguments,
> the result is an integer. How can I obtain floating point result (for more accuracy) while using
> "avg" or "stddev" function on integer arguments?
> Thank you,
> Yadnyesh.

It looks to me that is already does:

mydb=> \d+ test;
                       Table "public.test"
 Column  |            Type             | Modifiers | Description
---------+-----------------------------+-----------+-------------
 id_i    | integer                     | not null  |
 ir_id   | integer                     | not null  |
 test    | boolean                     |           |
 stamp   | timestamp without time zone |           |
 inttest | integer                     |           |
Indexes:
    "test_pk" PRIMARY KEY, btree (id_i, ir_id)
Has OIDs: no

mydb=> select * from test limit 5;
 id_i | ir_id | test |        stamp        | inttest
------+-------+------+---------------------+---------
    1 |     9 | f    | 2006-06-19 04:30:00 |       0
    1 |    10 | f    | 2006-06-19 05:30:00 |       0
    1 |     3 | t    | 2006-06-05 03:00:00 |       1
    2 |     4 | t    | 2006-06-05 04:00:00 |       1
    3 |     6 | t    | 2006-06-05 06:00:00 |       1
(5 rows)

mydb=> select avg(id_i) from test;
         avg
---------------------
 37.5679012345679012
(1 row)

mydb=> select stddev(id_i) from test;
       stddev
---------------------
 23.3285974029756760
(1 row)

Regards,

Richard Broersma Jr.

am  Sun, dem 19.11.2006, um 21:05:24 -0800 mailte Yadnyesh Joshi folgendes:
> According to the documentation, when "avg" or "stddev" function is used with
> integer arguments, the result is an integer. How can I obtain floating point

Thats not true:

test=> create table ints (i int);
CREATE TABLE
test=*> insert into ints values (2);
INSERT 0 1
test=*> insert into ints values (3);
INSERT 0 1
test=*> select avg(i) from ints;
        avg
--------------------
 2.5000000000000000
(1 row)

test=*> select stddev(i) from ints;
         stddev
------------------------
 0.70710678118654752440
(1 row)


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net