Received: from mail.interchange.ca (ns.interchange.ca [216.126.79.2]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5BIM7E26891 for ; Mon, 11 Jun 2001 14:22:07 -0400 (EDT) (envelope-from michael@fastmail.ca) Received: by mail.interchange.ca (Fastmailer, from userid 555) id DBC732114; Mon, 11 Jun 2001 14:22:01 -0400 (EDT) MIME-Version: 1.0 Message-Id: <3B250C49.0000FD.67770@frodo.searchcanada.ca> To: alex@pilosoft.com Subject: Re: finding a maximum or minimum sum Cc: pgsql-sql@postgresql.org From: "Michael Richards" X-Fastmail-IP: 24.43.130.237 Date: Mon, 11 Jun 2001 14:22:01 -0400 (EDT) Content-Transfer-Encoding: 7bit Content-Type: Text/Plain X-Archive-Number: 200106/166 X-Sequence-Number: 3388 > 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 ; 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: 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 >