pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
finding a maximum or minimum sum
5+ messages / 3 participants
[nested] [flat]

* finding a maximum or minimum sum
@ 2001-06-11 16:40 Michael Richards <michael@fastmail.ca>
  2001-06-11 17:49 ` Re: finding a maximum or minimum sum Alex Pilosov <alex@pilosoft.com>
  2001-06-11 18:46 ` Re: finding a maximum or minimum sum Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 5+ messages in thread

From: Michael Richards @ 2001-06-11 16:40 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: finding a maximum or minimum sum
  2001-06-11 16:40 finding a maximum or minimum sum Michael Richards <michael@fastmail.ca>
@ 2001-06-11 17:49 ` Alex Pilosov <alex@pilosoft.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Alex Pilosov @ 2001-06-11 17:49 UTC (permalink / raw)
  To: Michael Richards <michael@fastmail.ca>; +Cc: pgsql-sql

On Mon, 11 Jun 2001, Michael Richards wrote:

> 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?
select sum(amount),min(amount),max(amount) from payments group by userid

Is that what you mean? Or you want min/max _not_ to be grouped by userid?




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: finding a maximum or minimum sum
  2001-06-11 16:40 finding a maximum or minimum sum Michael Richards <michael@fastmail.ca>
@ 2001-06-11 18:46 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Tom Lane @ 2001-06-11 18:46 UTC (permalink / raw)
  To: Michael Richards <michael@fastmail.ca>; +Cc: pgsql-sql

"Michael Richards" <michael@fastmail.ca> writes:
> I run a select sum(amount) from payments group by userid
> I need to modify this query so it returns the minimum, maximum and 
> average sums. Is there any way I can do this?

You need two levels of grouping/aggregating to make that happen.
In 7.1 you can do it directly:

select min(amtsum), max(amtsum), avg(amtsum)
from (select sum(amount) as amtsum from payments group by userid) ss;

In prior versions you'd need to do the initial select into a temp
table and then select min/max/avg from that.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: finding a maximum or minimum sum
@ 2001-06-11 18:22 Michael Richards <michael@fastmail.ca>
  0 siblings, 0 replies; 5+ messages in thread

From: Michael Richards @ 2001-06-11 18:22 UTC (permalink / raw)
  To: alex@pilosoft.com; +Cc: pgsql-sql

> On Mon, 11 Jun 2001, Michael Richards wrote:
>
>> 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?
> select sum(amount),min(amount),max(amount) from payments group by
> userid
>
> Is that what you mean? Or you want min/max _not_ to be grouped by
> userid?

I wanted
Max $500
Min $20
Avg $276

The trouble of course is that these values are already operated upon 
by sum in the group.

-Michael
_________________________________________________________________
     http://fastmail.ca/ - Fast Free Web Email for Canadians
From pgsql-sql-owner@postgresql.org  Mon Jun 11 14:43:27 2001
Received: from kahuna.versions.com (kahuna.versions.com [204.17.38.231])
	by postgresql.org (8.11.3/8.11.1) with SMTP id f5BHsPE14003
	for <pgsql-sql@postgresql.org>; Mon, 11 Jun 2001 13:54:25 -0400 (EDT)
	(envelope-from pierre@kahuna.versions.com)
Received: (qmail 20148 invoked by uid 502); 11 Jun 2001 17:54:23 -0000
Message-ID: <20010611175423.20147.qmail@kahuna.versions.com>
From: pierre@kahuna.versions.com
Date: 11 Jun 2001 10:54:23 -0700
Subject: Re: Table design issue....
To: josh@agliodbs.com (Josh Berkus)
Date: Mon, 11 Jun 101 10:54:23 -0700 (MST)
Cc: pierre@kahuna.versions.com, pgsql-sql@postgresql.org
In-Reply-To: <web-70284@davinci.ethosmedia.com> from "Josh Berkus" at Jun 11, 1 10:44:01 am
Content-Type: text
X-Archive-Number: 200106/166
X-Sequence-Number: 3389

Wow, that is blunt. :-)

In all honesty, I can't imagine ever having to join all of that tables at once.
I had considered creating an attribute table previously, but was concerned
about performance when working to retrieve large numbers of records.

Perhaps, the best idea is the one I tried to not use, and that is a seperate
table that contains a copy of all of the attribute's that have the 
specified type. sort of a lookaside table. I was just concenred with
referential integrity.

Pierre

> 
> Pierre,
> 
> > Ideas? Comments? Suggestions? Am I being crazy?
> 
> Yes.  To be blunt, you've picked one of the worst possible database
> designs for any useful purpose.  This is, however, a common mistake as
> far too many books and training courses teach how to write SQL without
> teaching how to design a database.
> 
> What you really want is something like this:
> 
> CREATE TABLE tables (
> 	tableid CHAR(1) NOT NULL PRIMARY KEY
> 	);
> 
> CREATE TABLE attributes (
> 	tableid CHAR(1) NOT NULL REFERENCES tables(tableid),
> 	attributeid CHAR(1) NOT NULL,
> 	CONSTRAINT tab_attr_PK PRIMARY KEY (tableid, attributeid)	
> 	);
> 
> This makes your select statement possible:
> 	SELECT tableid FROM attributes WHERE attributeid = 'C'
> 	GROUP BY tableid ORDER BY tableid;
> 
> If your application requirements are more complicated than this, you
> need to either: a) hire a relational design expert, or b) become one.
> Books I'd recommend for the latter are Database Design for Mere Mortals
> and Practical Issues in Database Design (F. Pascal).
> 
> -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
> 




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: finding a maximum or minimum sum
@ 2001-06-11 19:03 Michael Richards <michael@fastmail.ca>
  0 siblings, 0 replies; 5+ messages in thread

From: Michael Richards @ 2001-06-11 19:03 UTC (permalink / raw)
  To: tgl@sss.pgh.pa.us; +Cc: pgsql-sql

Puuuurfect! This is exactly what I needed. Didn't know postgres 
supported subselects like that. Thanks.

-Michael

> select min(amtsum), max(amtsum), avg(amtsum)
> from (select sum(amount) as amtsum from payments group by userid)
> ss;
>
> In prior versions you'd need to do the initial select into a temp
> table and then select min/max/avg from that.

_________________________________________________________________
     http://fastmail.ca/ - Fast Free Web Email for Canadians
From pgsql-sql-owner@postgresql.org  Mon Jun 11 19:24:18 2001
Received: from bubbles.electricutopia.net (bubbles.electricutopia.net [63.214.178.76])
	by postgresql.org (8.11.3/8.11.1) with ESMTP id f5BNOFE37903;
	Mon, 11 Jun 2001 19:24:15 -0400 (EDT)
	(envelope-from dave@slickness.org)
Received: from localhost (dave@localhost)
	by bubbles.electricutopia.net (8.9.3/8.9.3) with ESMTP id QAA03026;
	Mon, 11 Jun 2001 16:23:29 -0700
Date: Mon, 11 Jun 2001 16:23:29 -0700 (PDT)
From: David Olbersen <dave@slickness.org>
X-X-Sender:  <dave@bubbles.electricutopia.net>
To: =?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= <martin@bugs.unl.edu.ar>
cc: pgsql sql Mailing List <pgsql-sql@postgresql.org>,
   <pgsql-general@postgresql.org>
Subject: Re: ORDER BY what?
In-Reply-To: <01061111193103.29653@bugs>
Message-ID: <Pine.LNX.4.31.0106111622110.2923-100000@bubbles.electricutopia.net>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by postgresql.org id f5BNOGE37905
X-Archive-Number: 200106/170
X-Sequence-Number: 3393

On Mon, 11 Jun 2001, Martín Marqués wrote:

> My question is: "How can I write an ORDER BY statment so that it's ordered by
> the amount of matches it gets?"

Your question is a bit unclear, what is "it"?
Specifically, what are the "it's" that are to be ordered, and which "it" gets
matches?

-- Dave




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2001-06-11 19:03 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-11 16:40 finding a maximum or minimum sum Michael Richards <michael@fastmail.ca>
2001-06-11 17:49 ` Alex Pilosov <alex@pilosoft.com>
2001-06-11 18:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-11 18:22 Re: finding a maximum or minimum sum Michael Richards <michael@fastmail.ca>
2001-06-11 19:03 Re: finding a maximum or minimum sum Michael Richards <michael@fastmail.ca>

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