Received: from mail.interchange.ca (ns.interchange.ca [216.126.79.2]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5BGenE79974 for ; Mon, 11 Jun 2001 12:40:49 -0400 (EDT) (envelope-from michael@fastmail.ca) Received: by mail.interchange.ca (Fastmailer, from userid 555) id DC3BD20CC; Mon, 11 Jun 2001 12:40:45 -0400 (EDT) MIME-Version: 1.0 Message-Id: <3B24F48D.000035.19989@frodo.searchcanada.ca> To: pgsql-sql@postgresql.org Subject: finding a maximum or minimum sum From: "Michael Richards" X-Fastmail-IP: 24.43.130.237 Date: Mon, 11 Jun 2001 12:40:45 -0400 (EDT) Content-Transfer-Encoding: 7bit Content-Type: Text/Plain X-Archive-Number: 200106/162 X-Sequence-Number: 3383 I have a table that looks like so: userid | amount --------------- 1 | $500 2 | $400 2 | $-100 2 | $10 3 | $10 3 | $10 I run a select sum(amount) from payments group by userid userid | sum -------------- 1 | $500 2 | $310 3 | $20 I need to modify this query so it returns the minimum, maximum and average sums. Is there any way I can do this? -Michael _________________________________________________________________ http://fastmail.ca/ - Fast Free Web Email for Canadians From pgsql-sql-owner@postgresql.org Mon Jun 11 13:50:31 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5BHNZE99087 for ; Mon, 11 Jun 2001 13:23:35 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 70261; Mon, 11 Jun 2001 10:23:33 -0700 From: "Josh Berkus" Subject: Re: problem with Pl/Pgsql function To: "Matteo Centenaro" , pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Mon, 11 Jun 2001 10:23:33 -0700 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200106/163 X-Sequence-Number: 3384 Matteo, > The error riported is: > > Fail to add null value in not null attribute prefisso > > The line which the error refere is: > > id_lista := nextval('tab_estrazioni_id_seq'); > insert into tab_estrazioni values (id_lista, pre, tel); > > Has Anybody any suggest? Thanks! While my Italian isn't really good enough to analyze your function, the above error seems simple. tab_estrazioni probably has the field prefisso, which is defined as NOT NULL with no default. In the above code, you're trying to add a record to tab_estrazioni filling in only the columns id_lista, pre, and tel. Without a default or a supplied value, this inserts NULL into the prefisso column, which the database rejects. Thus the solution is to either: a) Change prefisso to NULL, b) give prefisso a default, or c) supply a value for prefisso. Unfortunatley, the first two require dropping and re-creating the table. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco