From: Michael Richards <michael@fastmail.ca>
To: pgsql-sql@postgresql.org
Subject: finding a maximum or minimum sum
Date: Mon, 11 Jun 2001 12:40:45 -0400 (EDT)
Message-ID: <3B24F48D.000035.19989@frodo.searchcanada.ca> (raw)
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 <pgsql-sql@postgresql.org>; Mon, 11 Jun 2001 13:23:35 -0400 (EDT)
(envelope-from josh@agliodbs.com)
Received: from [209.10.40.250] (account <josh@agliodbs.com>)
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" <josh@agliodbs.com>
Subject: Re: problem with Pl/Pgsql function
To: "Matteo Centenaro" <gente_che_mixa@hotmail.com>, 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: <web-70261@davinci.ethosmedia.com>
In-Reply-To: <F1470xxDEA2VqFnW0kK00013ece@hotmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: michael@fastmail.ca
Subject: Re: finding a maximum or minimum sum
In-Reply-To: <3B24F48D.000035.19989@frodo.searchcanada.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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