pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedavg() on numeric ?
4+ messages / 3 participants
[nested] [flat]
* avg() on numeric ?
@ 1999-12-17 22:44 kaiq@realtyideas.com
0 siblings, 1 reply; 4+ messages in thread
From: kaiq@realtyideas.com @ 1999-12-17 22:44 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] avg() on numeric ?
@ 1999-12-17 23:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: kaiq@realtyideas.com
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 1999-12-17 23:14 UTC (permalink / raw)
To: kaiq@realtyideas.com; +Cc: pgsql-sql; wieck@debis.com
<kaiq@realtyideas.com> writes:
> 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
Hmm ... there were some similar bugs reported a couple of months ago,
and I thought Jan would've fixed them by now. Jan, if you don't have
time to look at this, let me know and I'll see if I can do anything
with it.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] avg() on numeric ?
@ 1999-12-17 23:43 Jan Wieck <wieck@debis.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: wieck@debis.com @ 1999-12-17 23:43 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: kaiq@realtyideas.com; pgsql-sql; wieck@debis.com
>
> <kaiq@realtyideas.com> writes:
> > 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
>
> Hmm ... there were some similar bugs reported a couple of months ago,
> and I thought Jan would've fixed them by now. Jan, if you don't have
> time to look at this, let me know and I'll see if I can do anything
> with it.
Looks to me there's something wrong with the adjustment of
actual computation and/or display precision during longer
taking aggregates. I know where to look at - tnx anyway.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] avg() on numeric ?
@ 1999-12-18 01:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jan Wieck <wieck@debis.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 1999-12-18 01:10 UTC (permalink / raw)
To: wieck@debis.com; +Cc: kaiq@realtyideas.com; pgsql-sql
wieck@debis.com (Jan Wieck) writes:
> Looks to me there's something wrong with the adjustment of
> actual computation and/or display precision during longer
> taking aggregates. I know where to look at - tnx anyway.
It doesn't necessarily take long; Zot O'Connor posted this example
in late October:
create table example(other decimal(4,4));
CREATE
insert into example (other) values(3.9);
ERROR: overflow on numeric ABS(value) >= 10^0 for field with precision 4 scale 4
insert into example (other) values(0.0);
ERROR: overflow on numeric ABS(value) >= 10^0 for field with precision 4 scale 4
which still fails in current sources.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-12-18 01:10 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-17 22:44 avg() on numeric ? kaiq@realtyideas.com
1999-12-17 23:14 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-17 23:43 ` Jan Wieck <wieck@debis.com>
1999-12-18 01:10 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox