Re: finding a maximum or minimum sum

> 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(at)postgresql(dot)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(at)postgresql(dot)org>; Mon, 11 Jun 2001 13:54:25 -0400 (EDT)
(envelope-from pierre(at)kahuna(dot)versions(dot)com)
Received: (qmail 20148 invoked by uid 502); 11 Jun 2001 17:54:23 -0000
Message-ID: <20010611175423(dot)20147(dot)qmail(at)kahuna(dot)versions(dot)com>
From: pierre(at)kahuna(dot)versions(dot)com
Date: 11 Jun 2001 10:54:23 -0700
Subject: Re: Table design issue....
To: josh(at)agliodbs(dot)com (Josh Berkus)
Date: Mon, 11 Jun 101 10:54:23 -0700 (MST)
Cc: pierre(at)kahuna(dot)versions(dot)com, pgsql-sql(at)postgresql(dot)org
In-Reply-To: <web-70284(at)davinci(dot)ethosmedia(dot)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(at)agliodbs(dot)com
> and data management solutions (415) 565-7293
> for law firms, small businesses fax 621-2533
> and non-profit organizations. San Francisco
>