pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
avg() 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