pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfinding 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>
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 17:49 Alex Pilosov <alex@pilosoft.com>
parent: Michael Richards <michael@fastmail.ca>
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 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 18:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Richards <michael@fastmail.ca>
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 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