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



view thread (5+ messages)  latest in thread

Message-ID: <3B24F48D.000035.19989@frodo.searchcanada.ca>
Permalink:  ../3B24F48D.000035.19989@frodo.searchcanada.ca/
Also on:    postgresql.org/message-id/3B24F48D.000035.19989@frodo.searchcanada.ca

 · 

reply

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