From: Michael Richards <michael@fastmail.ca>
To: alex@pilosoft.com
Cc: pgsql-sql@postgresql.org
Subject: Re: finding a maximum or minimum sum
Date: Mon, 11 Jun 2001 14:22:01 -0400 (EDT)
Message-ID: <3B250C49.0000FD.67770@frodo.searchcanada.ca> (raw)
> 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
>
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, alex@pilosoft.com
Subject: Re: finding a maximum or minimum sum
In-Reply-To: <3B250C49.0000FD.67770@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