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