avg() on numeric ?

From: <kaiq(at)realtyideas(dot)com>
To: pgsql-sql(at)postgreSQL(dot)org
Subject: avg() on numeric ?
Date: 1999-12-17 22:44:34
Message-ID: Pine.LNX.4.10.9912171638500.4620-100000@picasso.realtyideas.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


does avg() work on numeric ? it should, so, the more
accurate one should be: on PG 6.5.1 ?

please see below: (errors are on the last lines)
##########################################
create table salesreps (
empl_num integer not null,
name varchar (15) not null,
age integer,
rep_office integer,
title varchar (10),
hiredate date not null,
manager integer,
quota numeric(12,2), -- money,
sales numeric(12,2) --money
);

test=> select * from salesreps;
empl_num|name |age|rep_office|title | hiredate|manager| quota| sales
--------+-------------+---+----------+---------+----------+-------+---------+---------

105|Bill Adams | 37| 13|Sales Rep|02-12-1988| 104|350000.00|367911.00
109|Mary Jones | 31| 11|Sales Rep|10-12-1989| 106|300000.00|392725.00
102|Sue Smith | 48| 21|Sales Rep|12-10-1986| 108|350000.00|474050.00
106|Sam Clark | 52| 11|VP Sales |06-14-1988| |275000.00|299912.00
104|Bob Smith | 33| 12|Sales Mgr|05-19-1987| 106|200000.00|142594.00
101|Dan Roberts | 45| 12|Sales Rep|10-20-1986| 104|300000.00|305673.00
110|Tom Snyder | 41| |Sales Rep|01-13-1990| 101| | 75985.00
108|Larry Fitch | 62| 21|Sales Mgr|10-12-1989| 106|350000.00|361865.00
103|Paul Cruz | 29| 12|Sales Rep|03-01-1987| 104|275000.00|286775.00
107|Nancy Angelli| 49| 22|Sales Rep|11-14-1988| 108|300000.00|186042.00
(10 rows)

test=> select sum(quota) from salesreps;
sum
----------
2700000.00
(1 row)

test=> select count(quota) from salesreps;
count
-----
9
(1 row)

test=> select sum(quota)/count(quota) from salesreps;
?column?
-----------------
300000.0000000000
(1 row)

test=> select avg(quota) from salesreps;
ERROR: overflow on numeric ABS(value) >= 10^-1 for field with precision 0 scale 1723

test=> select avg(sales) from salesreps;
ERROR: overflow on numeric ABS(value) >= 10^-1 for field with precision 0
scale 1723

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 1999-12-17 23:14:18 Re: [SQL] avg() on numeric ?
Previous Message Bruce Momjian 1999-12-17 22:05:33 Re: [SQL] Sub-select speed.